Re: Logging
On Wed, 4 Dec 2019, Adrian Klaver wrote: Take a look at the logging section of postgresql.conf to see if Postgres is handing off to the system and logrotate Adrian, That conf file is in several places (different flavors). I'll check them all. Thanks, Rich
Re: Logging [RESOLVED]
On Wed, 4 Dec 2019, Stephen Eilert wrote: Usually, this is done by logrotate or a similar mechanism in your system. You’ll likely find that other logs in your system follow a similar pattern, not just Postgresql. Stephen, Other logs, controlled by logrotate, rotate daily for a maximum of 4 backups. I just checked /etc/logrotate.d/postgres and it was set at daily with rotate 7. I changed rotate to 4 but the dates are 3-4 days apart, not sequential. Thanks, Rich
Re: Logging
On 12/4/19 3:20 PM, Rich Shepard wrote: Running Slackware-14.2/x86_64 and postgresql-11.5. In /var/log/ are these files: -rw-r- 1 postgres wheel 0 Nov 23 04:40 postgresql-11 -rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1 -rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz -rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz -rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz -rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz -rw-r- 1 postgres wheel 325 Nov 6 04:40 postgresql-11.6.gz -rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz I assume that they're an automatic backup that runs every 3-4 days. What's backed up and where is this controlled? Looks like logrotate. Take a look at the logging section of postgresql.conf to see if Postgres is handing off to the system and logrotate I ask because I have a cron job that does a pg_dumpall each night at 11:30 pm. (It's a small installation for my business use so the files are not excessive and I keep them for only short periods.) Regards, Rich -- Adrian Klaver adrian.kla...@aklaver.com
Re: Logging
Usually, this is done by logrotate or a similar mechanism in your system. You’ll likely find that other logs in your system follow a similar pattern, not just Postgresql. — Stephen On Dec 4, 2019, 3:21 PM -0800, Rich Shepard , wrote: > Running Slackware-14.2/x86_64 and postgresql-11.5. > > In /var/log/ are these files: > > -rw-r- 1 postgres wheel 0 Nov 23 04:40 postgresql-11 > -rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1 > -rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz > -rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz > -rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz > -rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz > -rw-r- 1 postgres wheel 325 Nov 6 04:40 postgresql-11.6.gz > -rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz > > I assume that they're an automatic backup that runs every 3-4 days. What's > backed up and where is this controlled? > > I ask because I have a cron job that does a pg_dumpall each night at 11:30 > pm. (It's a small installation for my business use so the files are not > excessive and I keep them for only short periods.) > > Regards, > > Rich > >
Logging
Running Slackware-14.2/x86_64 and postgresql-11.5. In /var/log/ are these files: -rw-r- 1 postgres wheel 0 Nov 23 04:40 postgresql-11 -rw-r- 1 postgres wheel 723 Nov 23 04:40 postgresql-11.1 -rw-r- 1 postgres wheel 324 Nov 20 04:40 postgresql-11.2.gz -rw-r- 1 postgres wheel 320 Nov 17 04:40 postgresql-11.3.gz -rw-r- 1 postgres wheel 322 Nov 14 04:40 postgresql-11.4.gz -rw-r- 1 postgres wheel 321 Nov 10 04:40 postgresql-11.5.gz -rw-r- 1 postgres wheel 325 Nov 6 04:40 postgresql-11.6.gz -rw-r- 1 postgres wheel 337 Oct 23 04:40 postgresql-11.7.gz I assume that they're an automatic backup that runs every 3-4 days. What's backed up and where is this controlled? I ask because I have a cron job that does a pg_dumpall each night at 11:30 pm. (It's a small installation for my business use so the files are not excessive and I keep them for only short periods.) Regards, Rich
Re: what causes xact_rollback for a database in pg_stat_database to increment?
"Hu, Patricia" writes: > We’ve been seeing over 200k rollbacks being recorded for a database > at certain hour overnight but can’t see the corresponding ROLLBACK > statements being recorded in postgresql db logs, even though our > log_statement is set to ALL. > > > > I did some tests and confirmed that xact_rollback count increments in > scenarios below and am looking further at those. > > 1. a ROLLBACK is issued explicitly or implicitly(if a > transaction or pl/pgsql block/function raises an exception or was > aborted) > > 2. it doesn’t reflect the number of records being rolled back > > 3. even if ROLLBACK statement results in WARNING like “there is > no transaction in progress”, it increments > > > > Does anyone know on top of his/her head any (other) explanation for > such unaccounted for rollbacks? Dangling session... begin; select this; select that... HTH > > > > Thanks, > > Patricia > > > > Confidentiality Notice:: This email, including attachments, may > include non-public, proprietary, confidential or legally privileged > information. If you are not an intended recipient or an authorized > agent of an intended recipient, you are hereby notified that any > dissemination, distribution or copying of the information contained > in or transmitted with this e-mail is unauthorized and strictly > prohibited. If you have received this email in error, please notify > the sender by replying to this message and permanently delete this > e-mail, its attachments, and any copies of it immediately. You should > not retain, copy or use this e-mail or any attachment for any > purpose, nor disclose all or any part of the contents to any other > person. Thank you. > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net
Re: what causes xact_rollback for a database in pg_stat_database to increment?
Hi Hu Log_statement = all can miss some statements sent to Postgresql from the manual https://www.postgresql.org/docs/current/runtime-config-logging.html *Note* *Statements that contain simple syntax errors are not logged even by the log_statement = all setting, because the log message is emitted only after basic parsing has been done to determine the statement type. In the case of extended query protocol, this setting likewise does not log statements that fail before the Execute phase (i.e., during parse analysis or planning). Set log_min_error_statement to ERROR (or lower) to log such statements.* Trying cranking up Log_min_error_Statement Also you can monitor the number of transactions being created from PG10+ there is txid_status() https://www.2ndquadrant.com/en/blog/postgresql-10-transaction-traceability/ On Wed, Dec 4, 2019 at 3:49 PM Hu, Patricia wrote: > We’ve been seeing over 200k rollbacks being recorded for a database at > certain hour overnight but can’t see the corresponding ROLLBACK statements > being recorded in postgresql db logs, even though our log_statement is set > to ALL. > > > > I did some tests and confirmed that xact_rollback count increments in > scenarios below and am looking further at those. > > 1. a ROLLBACK is issued explicitly or implicitly(if a transaction > or pl/pgsql block/function raises an exception or was aborted) > > 2. it doesn’t reflect the number of records being rolled back > > 3. even if ROLLBACK statement results in WARNING like “there is no > transaction in progress”, it increments > > > > Does anyone know on top of his/her head any (other) explanation for such > unaccounted for rollbacks? > > > > Thanks, > > Patricia > > > Confidentiality Notice:: This email, including attachments, may include > non-public, proprietary, confidential or legally privileged information. If > you are not an intended recipient or an authorized agent of an intended > recipient, you are hereby notified that any dissemination, distribution or > copying of the information contained in or transmitted with this e-mail is > unauthorized and strictly prohibited. If you have received this email in > error, please notify the sender by replying to this message and permanently > delete this e-mail, its attachments, and any copies of it immediately. You > should not retain, copy or use this e-mail or any attachment for any > purpose, nor disclose all or any part of the contents to any other person. > Thank you. >
what causes xact_rollback for a database in pg_stat_database to increment?
We've been seeing over 200k rollbacks being recorded for a database at certain hour overnight but can't see the corresponding ROLLBACK statements being recorded in postgresql db logs, even though our log_statement is set to ALL. I did some tests and confirmed that xact_rollback count increments in scenarios below and am looking further at those. 1. a ROLLBACK is issued explicitly or implicitly(if a transaction or pl/pgsql block/function raises an exception or was aborted) 2. it doesn't reflect the number of records being rolled back 3. even if ROLLBACK statement results in WARNING like "there is no transaction in progress", it increments Does anyone know on top of his/her head any (other) explanation for such unaccounted for rollbacks? Thanks, Patricia Confidentiality Notice:: This email, including attachments, may include non-public, proprietary, confidential or legally privileged information. If you are not an intended recipient or an authorized agent of an intended recipient, you are hereby notified that any dissemination, distribution or copying of the information contained in or transmitted with this e-mail is unauthorized and strictly prohibited. If you have received this email in error, please notify the sender by replying to this message and permanently delete this e-mail, its attachments, and any copies of it immediately. You should not retain, copy or use this e-mail or any attachment for any purpose, nor disclose all or any part of the contents to any other person. Thank you.
Re: archiving question
Greetings, * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > When there is a Postgres archiver stuck because of filled pg_xlog and archive > directories... > > ... and the pg_xlog directory had been filled with dozens of GBs of xlogs... > > ...it takes ages until the archive_command had moved all xlogs from the > pg_xlog directory to the archive directory afterwards... > > ... and you get crazy if you have a 8GB archive directory while the pg_xlog > directory had been pumped up to 100GB :( > > > Any idea on this one? Parallelizing the archive-push operation can be quite helpful to address this. Thanks, Stephen signature.asc Description: PGP signature
archiving question
When there is a Postgres archiver stuck because of filled pg_xlog and archive directories... ... and the pg_xlog directory had been filled with dozens of GBs of xlogs... ...it takes ages until the archive_command had moved all xlogs from the pg_xlog directory to the archive directory afterwards... ... and you get crazy if you have a 8GB archive directory while the pg_xlog directory had been pumped up to 100GB :( Any idea on this one?
Re: upgrade and migrate
On 2019-12-04 08:56, Laurenz Albe wrote: On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote: On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote: Hello, what is the best way to migrate from PostgreSQL 8.3.11 on x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu server, with minimal downtime? The caveat is the source has about 80 databases overall almost 30 TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and pg_restore, but the largest hot database is almost 17 tb, and I am not sure how to approach this effort in a better and efficient way? pg_upgrade could be one way to go here. That's not the scale pg_dump would be very good at. I would have personally avoided using pg_dump above 10~20GB. Depending on the downtime you are ready to accept, a migration based on Slony could be something to investigate. Right, Slony is the way to go, since pg_upgrade doesn't support 8.3. Also consider Londiste. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: secure deletion of archived logs
Greetings, * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote: > with Oracle we use "backup archivelog all delete all input". > this is a kind of atomic transaction. > everything backuped for sure is deleted. > > with Postgres we archive to a local host directory ... how? Do you actually sync the files after you copy them with an fsync to be sure that they're durably stored there? If not, then there's a pretty good chance that you'll lose some WAL if a crash happens because if your archive command returns successful, PG will removed its copy of the WAL file. Hint: using 'cp' as an archive command is a very bad idea. > we do a Networker backup of this directory afterwards and delete the archived > logs > but this is not an atomic transaction > so there is a small risk that something gets deleted which is not backuped That would definitely be quite bad, particularly if a WAL file that was needed for a backup to be consistent was removed or missed, as that backup would no longer be valid then. > how to you prevent this? I would strongly recommend that you use a tool that's actually built for the purpose of backing up PG systems, like pgbackrest or similar. Writing your own custom code for managing WAL archives and backup sets is likely to result in issues. > Is there any backup tool which can do backups analogous Oracle? There's quite a few different tools available for backing up PG systems, with various features and performance- from simple things like pg_basebackup (which you can set up to include all the WAL for the backup to be consistent, though that doesn't do anything to help you with managing WAL for PITR), to much more sophisticated tools like pgbackrest, wal-g, and others that help with managing WAL and dealing with expiring out backups and such. The biggest thing is- don't try to roll your own. Thanks, Stephen signature.asc Description: PGP signature
Re: upgrade and migrate
Stephen Frost writes: > * Laurenz Albe (laurenz.a...@cybertec.at) wrote: >> Right, Slony is the way to go, since pg_upgrade doesn't support 8.3. >> I would upgrade to a version more recent than 9.6. > So... there's a bit of history here. pg_upgrade in 9.4 actually does > support upgrading from 8.3.X. Support for upgrading from 8.3 was > removed in 2209b3923a7afe0b6033ecfea972219df252ca8e. Yeah. Also note that 8.3 to 9.6-or-newer is going to be a pretty huge jump in terms of minor compatibility issues (have you read all the relevant release notes?). So there's something to be said for breaking this down into two steps: update to 9.4, test/fix your applications against that, then make a second jump to something current. Each of those jumps could be handled by the respective version of pg_upgrade. I concur with Laurenz's advice that stopping at 9.6 is probably not your best choice for a migration today. regards, tom lane
Re: upgrade and migrate
Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > On Wed, 2019-12-04 at 13:48 +0900, Michael Paquier wrote: > > On Tue, Dec 03, 2019 at 10:32:22PM +, Julie Nishimura wrote: > > > Hello, what is the best way to migrate from PostgreSQL 8.3.11 on > > > x86_64-redhat-linux-gnu to PostgreSQL 9.6.16 on x86_64-pc-linux-gnu > > > server, with minimal downtime? > > > The caveat is the source has about 80 databases overall almost 30 > > > TB. I could migrate the smallest ones (up to 1 tb) using pg_dump and > > > pg_restore, but the largest hot database is almost 17 tb, and I am > > > not sure how to approach this effort in a better and efficient way? > > > > pg_upgrade could be one way to go here. That's not the scale pg_dump > > would be very good at. I would have personally avoided using pg_dump > > above 10~20GB. Depending on the downtime you are ready to accept, > > a migration based on Slony could be something to investigate. > > Right, Slony is the way to go, since pg_upgrade doesn't support 8.3. > > I would upgrade to a version more recent than 9.6. So... there's a bit of history here. pg_upgrade in 9.4 actually does support upgrading from 8.3.X. Support for upgrading from 8.3 was removed in 2209b3923a7afe0b6033ecfea972219df252ca8e. Thanks, Stephen signature.asc Description: PGP signature
secure deletion of archived logs
with Oracle we use "backup archivelog all delete all input". this is a kind of atomic transaction. everything backuped for sure is deleted. with Postgres we archive to a local host directory we do a Networker backup of this directory afterwards and delete the archived logs but this is not an atomic transaction so there is a small risk that something gets deleted which is not backuped how to you prevent this? Is there any backup tool which can do backups analogous Oracle?
Re: upgrade and migrate
On Wed, Dec 04, 2019 at 08:38:01AM +0100, Thomas Kellerer wrote: > But pg_upgrade only supports 8.4+ Ditto. You're right here. -- Michael signature.asc Description: PGP signature
