Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Time of completion of last Postgres Checkpoint

K
kalena last edited by

Is there a psql way to get the completion time (and other details) for latest Postgres checkpoint?
Reply Quote 0
1 Reply Last reply

meriah

You can get the details of the latest checkpoint, including the time, from the pg_control_checkpoint() function:

SELECT * FROM pg_control_checkpoint() \gx
─[ RECORD 1 ]────────┬─────────────────────────

checkpoint_lsn       │ B/AF0C2EB8

redo_lsn             │ B/AF0C2E80

redo_wal_file        │ 000000040000000B000000AF

timeline_id          │ 4

prev_timeline_id     │ 4

full_page_writes     │ t

next_xid             │ 0:16691338

next_oid             │ 559103

next_multixact_id    │ 16340004

next_multi_offset    │ 326571432

oldest_xid           │ 726

oldest_xid_dbid      │ 1

oldest_active_xid    │ 16691338

oldest_multi_xid     │ 1

oldest_multi_dbid    │ 1

oldest_commit_ts_xid │ 0

newest_commit_ts_xid │ 0

checkpoint_time      │ 2022-05-23 08:18:02+02

postgresql 17192

2
Posts

0
Views

Suggested Topics

A

How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql
SQL, Database Testing • postgresql storage disk spac languagee multi tenant • • authera

2

0
Votes

2
Posts

0
Views

C

Assuming that the average size of a single row is somewhat comparable between tenants, something like the following: select tenant_id, pg_total_relation_size('the_table')::decimal * (count(*)::decimal / (select count(*) from the_table)) as approx_tenant_size from the_table group by tenant_id; That will calculate size per tenant based on the percentage of rows for that tenant. This will however return completely wrong estimates if you have large text or varchar columns and some tenants only store short values in there and some really large values.
S

Notice of successfully recorded in the bank - R
Software Programming • postgresql • • shizuka

2

0
Votes

2
Posts

0
Views

Here’s how to generate a simple log file, which records the date and time and any error message:logf <- file("exemplo.log", open = "a") writeLines(as.character(Sys.time()), logf) try(dbWriteTable(con, "tabela", tabela), outFile = logf) close(logf) If you need a more detailed registry, see sink, which redirects the terminal output to a connection.For error alert, you can use tryCatch and run a system command. For example, to warn all users logged into a *nix server:tryCatch(dbWriteTable(con, "tabela", tabela), error = system("wall 'Erro na gravação do DB'")) For a message on the screen of your graphic environment, an option is the svDialogs package:tryCatch(dbWriteTable(con, "tabela", tabela), error = svDialogs::msg_box("Erro na gravação do DB"))
R

How do I shorten validation time?
SQL, Database Testing • sql server sql server 2014 ssis • • Rossere

2

0
Votes

2
Posts

0
Views

I think that your OLE DB source is causing the issue Create a new table with the same schema as your current source and point the component at it. Within the package, update to your actual source, as this will force it to handle it at run-time. The easiest way I have found to do this is to create a variable and assign it to the source expression. See an example of this at https://stackoverflow.com/questions/18288183/parameterized-oledb-source-query Another possible solution is to set the DelayValidation property of the Data Flow task to True - See https://technet.microsoft.com/en-us/library/aa337088(v=sql.105).aspx#Anchor_4
K

Silent installation of Postgres and creation of the base in installer
Software Programming • c sharp postgresql winforms installation • • kalyn

2

0
Votes

2
Posts

0
Views

K

If you call the executable of the postgresql passing as parameter --help will open the information: You should inform the password, the path of the installation, this you can set through the parameters.So you can call it this way:msiexec /i postgresql[versão].msi /qr INTERNALLAUNCH=1 ADDLOCAL=server,psql,docs SERVICEDOMAIN="%COMPUTERNAME%" SERVICEPASSWORD="SecretWindowsPassword123" SUPERPASSWORD="VerySecret" BASEDIR="c:\postgres" TRANSFORMS=:lang_de OBS: I've never used the Advanced Installer, but I've already used the http://nsis.sourceforge.net/Main_Page , and currently use the http://www.jrsoftware.org/isinfo.php , I know what to do without a problem..Reference: http://pginstaller.projects.pgfoundry.org/silent.html I hope I helped!
A

Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"
SQL, Database Testing • postgresql authentication backup configuration • • authera

2

0
Votes

2
Posts

0
Views

G

In pg_hba.conf, change this line: local all all peer to: local all all password Then you can log in locally using a password.
C

Migrate complete postgres database cluster to another machine using pg_dump
SQL, Database Testing • postgresql backup restore migration pg dump • • charlenef

2

0
Votes

2
Posts

0
Views

E

The question you are referencing is a bit of a mess, I think you would be better off not referring to it at all. It just introduces misconceptions to then partially remove some of them. If you want to backup and restore the whole cluster, you should use "pg_dumpall", not "pg_dump". Using "pg_dumpall" without any of the restrictive options will emit the necessary "CREATE DATABASE" commands. If you run "pg_dump" with -C option, it too will emit "CREATE DATABASE" commands. I would like to be able to restore single databases later on, so pg_dumpall doesn't seem to be the right way, or is it? If you want to do something different in the future, then do something different in the future. "pg_dumpall" preserves your options. You can restore the whole thing, and then drop databases you don't want. Or you can restore the whole, and re-dump what you do want out of it. Or just dump what you want out of the original server, if that is still around. Or use vi or sed or awk or perl to munge the "pg_dumpall" .sql file so that it does only what you want. If you don't like any of those options, you could do a pg_dumpall -g accompanied with a pg_dump -C of each individual database. If your primary use case to restore a single database but without knowing ahead of time which one it will be, this would be faster than the previous options, and could be run on a smaller machine than the original. But it is also a bit harder to document, and more likely for files to get out of sync with each other.
R

How to convert mysql to postgresql?
SQL, Database Testing • mysql postgresql linux • • rielyn

2

0
Votes

2
Posts

0
Views

C

Migrating between two very different DBMS requires a lot more than just migrating the data. But migration of the data is usually the easiest part. The way that I have tried, which is free and I can confirm that it works: create a mysql schema only dump Adjust the SQL using a text editor and a lot of search and replace run the converted SQL in Postgres Create a plain text dump from MySQL (e.g. CSV, or some other delimited format) import the data using PostgreSQL's COPY command Importing the data might actually be difficult if you relied on MySQL's behaviour to accept illegal data (like the 31st of February) My guess is, that this is going to be quicker than searching for a tool, evaluating a bunch of them and then trying to understand the one that you chose. But it depends on what kind of "big" you are referring to. If big is several hundreds of tables this might not be feasible. If big only referred to the number of rows, then this is probably the quickest way to do it. There are some tools out there that can dump a database schema in a DBMS independent (XML) format, like Liquibase, SchemaSpy or WbSchemaReport. With Liquibase probably being the easiest to use. The others will require some manual work writing/extending the XSLT to transform the generated XML. If you are using triggers and stored procedures in MySQL, I don't believe there will be any automated tool that can translate them without requiring a major manual fixing afterwards - and then the generated procedures would probably not use any advanced features of the target DBMS.
R

is it possible to run postgres with no WAL files being produced?
SQL, Database Testing • postgresql postgresql 9.2 • • Rossere

2

0
Votes

2
Posts

0
Views

Y

Short version: no. PostgreSQL doesn't support generating no WAL whatsoever, because changes to system catalog tables in the pg_catalog schema are always logged. There's also transaction ID logging (pg_clog), the multixact tracking data (pg_multixact), etc, in addition to the xlog, but they tend to be very small. As Daniel says, you can run with minimal WAL generation by: Using wal_level = minimal Creating all tables as UNLOGGED or TEMPORARY tables In this case, you will also want to set fsync = off, full_page_writes = off and synchronous_commit = off, since you have no requirement for durability and crash recovery. Of course, if anything goes wrong (like power loss, unplanned restart, DB server crash, pg_ctl -m immediate stop, etc) your data will be totally unrecoverable, but that seems to be what you want. The rate of WAL creation and rotation with this configuration will be negligible for most applications. If you don't mind having to re-initdb after any restart (even a clean one) you could put pg_xlog on a tempfs, but I doubt it's worth the hassle when Pg will generate only a tiny bit of WAL. You should also check and make sure you don't have WAL archiving (archive_mode = on) enabled, that wal_keep_segments isn't set. Both of those are only useful if you're doing WAL-based backup or replication. If you don't, then WAL should not accumulate, it should be rapidly recycled. Maybe your checkpoints are set to be much too infrequent, so much so that you run out of disk? Check to see if the checkpoint_segments parameter is really really high. I think you're trying to solve the wrong problem here. The issue isn't stopping PostgreSQL from producing WAL, it's figuring out why it piled up in your batch jobs. See also: Optimizing PostgreSQL for fast testing.
B

Postgresql - PHP - Run Array and insert in database depending on each line of the array
Software Programming • sql postgresql array select insert • • baileigh

2

0
Votes

2
Posts

0
Views

R

I welcome you to Stackoverflow.Inside it while that reads the first results you can build the multiple INSERT in a variable, and then you pass that variable to the handler to insert the data.It would just be this:/* Variable que reunirá los INSERT */ $multiInsert=""; while ($row = pg_fetch_array($sql)) { $fecha = $row['fecha']; $tramo = $row['tramo']; $sentido = $row['sentido']; /* Concatenamos cada fila con .= NÓTESE que cada sentencia de la cadena termina con ; (dentro de las comillas) */ $multiInsert.="INSERT INTO tabla2(id, fecha, flujo,tramo) SELECT id,fecha,flujo,tramo FROM test2 where tramo = '$tramo' and fecha = '$fecha';"; } Then you run:pg_query($multiInsert); Some observations:If the values $tramo and $fecha are within reach of the user and can be modified by the user, you should use prepared queries, otherwise the code is vulnerable to attacks of SQL injection.If the column tramo It's numerical, you have to put the consultation like this: where tramo = $tramoI mean, not around $tramo from simple quotes.For massive inserts or modifications, it is appropriate to use transactions.
How do I copy databases from Postgres 8.4 to Postgres 9.1?
SQL, Database Testing • postgresql upgrade rec languageovery • • irl

2

0
Votes

2
Posts

0
Views

M

It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment. So, here are what I see as your options: Using pg_upgrade On The New Server (pg_upgrade) Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt. Attempt to start the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/ If that works, stop the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/ Now try your pg_upgrade script Without Using pg_upgrade Create a VM or use an old, decommed server and install postgresql 8.4 Initialize your server using initdb (or pg_createcluster) Copy your backed up data over that which was just created Start postgresql Backup the data using pg_dump Copy the dump file to the "new server" Restore to a new database on the new server
K

Postgres: Subquery with GROUP BY
SQL, Database Testing • postgresql • • Kadyn

2

0
Votes

2
Posts

0
Views

K

Was solved using window functions, like: SELECT e.* FROM (SELECT e.pageview_identifier, e.created_at, e.pageview_current_url, e.pageview_mobile, e.event_type, COUNT(*) FILTER (WHERE e.event_type = 'mouse:move') OVER (PARTITION BY e.company_identifier, e.user_identifier, e.pageview_identifier) as cnt_mouse_move, ... FROM events e WHERE e.company_identifier = 'xyz' AND e.user_identifier = '01CDQZVSJFBDA8W444JS2CS3BA' ) e WHERE e.event_type = 'page:view';
A

Postgres database not using indexes
SQL, Database Testing • postgresql • • Avante

2

0
Votes

2
Posts

0
Views

T

I suggest to improve the index to: (timestamp, id) WHERE (imported = false) so it fits the query better, for both the WHERE and ORDER BY clauses. The plan will only need an index seek and a partial index scan (until it finds the required 1000 rows). The current index does not hold the id values, so the whole index has to be read and then the id values retrieved from the table and sorted to get the first 1000. If the rows matching the WHERE conditions are not many (less than 1000 or less than 10k), then the sort operation is not very expensive but the values still have to be read as they are not in the index. If the matching rows are a lot more (say 100K or more), then both fetching and sorting them will be much more expensive. It might be cheaper in these cases to do a full table scan. The optimizer decided in your case to do something similar: a parallel index scan of the PK index. This avoids the need of sorting but it still has to read the rows from the table until it finds 1000 rows that match the WHERE condition. Both cases however would have no problem with the suggested index above, as the table would not have to be read at all and no sorting needed as the index order matches the ORDER BY.
S

How to create automatic partitions on a non-time based column in Postgres?
SQL, Database Testing • postgresql partitioning hashing • • shizuka

2

0
Votes

2
Posts

0
Views

J

There is nothing built into Postgres to do "automatic partition creation", so you either need to write a little script that automates that process or use a tool like pg_partman if it supports that. In psql you could use a query like this to generate the create table statements and then run it using \g: select format('create table %I partition of my_table for values with (modulus 10, remainder %s);', 'my_table_p'||to_char(g.i, 'FM00'), g.i) from generate_series(0,9) as g(i) \g Otherwise just use a do block: do $$ declare l_sql text; begin for i in 0..9 loop l_sql := format('create table %I partition of my_table for values with (modulus 10, remainder %s)', 'my_table_p'||to_char(i, 'FM00'), i); execute l_sql; end loop; end; $$ ; Not tested! Of course you can put the PL/pgSQL loop into a function or procedure and call it with the number of partitions you want.
How to use full text search in Persian using Postgresql?
SQL, Database Testing • postgresql full text searc languageh • • Mystic

2

0
Votes

2
Posts

0
Views

M

use this. select post_id from comments where to_tsvector('simple', comments.body) @@ to_tsquery('simple', 'کتاب') ; OR select post_id from comments where to_tsvector('simple', comments.body) @@ to_tsquery('کتاب') ; Or use this extension: https://pgroonga.github.io/tutorial/
M

How to use grouping sets in this query that shows database sizes - adding the last time it was updated
SQL, Database Testing • sql server sql server 2016 query group by automation • • meriah

2

0
Votes

2
Posts

0
Views

C

I just removed the DB_Name from Grouping SELECT [Database Name] = DB_NAME(s.database_id), [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)' WHEN s.Type_Desc = 'LOG' THEN 'Log File(s)' ELSE s.Type_Desc END, LastUpdate= case when s.type_desc ='ROWS' then NULL when s.type_desc = 'LOG' then NULL else (select COALESCE(max(last_user_update),MAX(last_system_update)) from sys.dm_db_index_usage_stats dius where dius.database_id = s.database_id) end, [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ), [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) ) FROM sys.master_files s GROUP BY GROUPING SETS ( (s.database_id, s.Type_Desc), (s.database_id) ) ORDER BY DB_NAME(s.database_id), s.Type_Desc DESC output: Database Name Type lastUpdate Size in MB Size in GB db_xxx Log File(s) Null 8.00 0.01 db_xxx Data File(s) Null 8.00 0.01 db_xxx NULL 2019-07-14 17:27:04.473 16.00 0.02

Time of completion of last Postgres Checkpoint

Suggested Topics

How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql SQL, Database Testing • postgresql storage disk spac languagee multi tenant • • authera

Notice of successfully recorded in the bank - R Software Programming • postgresql • • shizuka

How do I shorten validation time? SQL, Database Testing • sql server sql server 2014 ssis • • Rossere

Silent installation of Postgres and creation of the base in installer Software Programming • c sharp postgresql winforms installation • • kalyn

Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres" SQL, Database Testing • postgresql authentication backup configuration • • authera

Migrate complete postgres database cluster to another machine using pg_dump SQL, Database Testing • postgresql backup restore migration pg dump • • charlenef

How to convert mysql to postgresql? SQL, Database Testing • mysql postgresql linux • • rielyn

is it possible to run postgres with no WAL files being produced? SQL, Database Testing • postgresql postgresql 9.2 • • Rossere

Postgresql - PHP - Run Array and insert in database depending on each line of the array Software Programming • sql postgresql array select insert • • baileigh

How do I copy databases from Postgres 8.4 to Postgres 9.1? SQL, Database Testing • postgresql upgrade rec languageovery • • irl

Postgres: Subquery with GROUP BY SQL, Database Testing • postgresql • • Kadyn

Postgres database not using indexes SQL, Database Testing • postgresql • • Avante

How to create automatic partitions on a non-time based column in Postgres? SQL, Database Testing • postgresql partitioning hashing • • shizuka

How to use full text search in Persian using Postgresql? SQL, Database Testing • postgresql full text searc languageh • • Mystic

How to use grouping sets in this query that shows database sizes - adding the last time it was updated SQL, Database Testing • sql server sql server 2016 query group by automation • • meriah

How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql
SQL, Database Testing • postgresql storage disk spac languagee multi tenant • • authera

Notice of successfully recorded in the bank - R
Software Programming • postgresql • • shizuka

How do I shorten validation time?
SQL, Database Testing • sql server sql server 2014 ssis • • Rossere

Silent installation of Postgres and creation of the base in installer
Software Programming • c sharp postgresql winforms installation • • kalyn

Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"
SQL, Database Testing • postgresql authentication backup configuration • • authera

Migrate complete postgres database cluster to another machine using pg_dump
SQL, Database Testing • postgresql backup restore migration pg dump • • charlenef

How to convert mysql to postgresql?
SQL, Database Testing • mysql postgresql linux • • rielyn

is it possible to run postgres with no WAL files being produced?
SQL, Database Testing • postgresql postgresql 9.2 • • Rossere

Postgresql - PHP - Run Array and insert in database depending on each line of the array
Software Programming • sql postgresql array select insert • • baileigh

How do I copy databases from Postgres 8.4 to Postgres 9.1?
SQL, Database Testing • postgresql upgrade rec languageovery • • irl

Postgres: Subquery with GROUP BY
SQL, Database Testing • postgresql • • Kadyn

Postgres database not using indexes
SQL, Database Testing • postgresql • • Avante

How to create automatic partitions on a non-time based column in Postgres?
SQL, Database Testing • postgresql partitioning hashing • • shizuka

How to use full text search in Persian using Postgresql?
SQL, Database Testing • postgresql full text searc languageh • • Mystic

How to use grouping sets in this query that shows database sizes - adding the last time it was updated
SQL, Database Testing • sql server sql server 2016 query group by automation • • meriah