Re: upgrade and migrate

2019-12-04 Thread Michael Paquier
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

secure deletion of archived logs

2019-12-04 Thread Zwettler Markus (OIZ)
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 atom

Re: upgrade and migrate

2019-12-04 Thread Stephen Frost
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 Postg

Re: upgrade and migrate

2019-12-04 Thread Tom Lane
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

Re: secure deletion of archived logs

2019-12-04 Thread Stephen Frost
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 actuall

Re: upgrade and migrate

2019-12-04 Thread Peter Eisentraut
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-gn

archiving question

2019-12-04 Thread Zwettler Markus (OIZ)
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

Re: archiving question

2019-12-04 Thread Stephen Frost
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

what causes xact_rollback for a database in pg_stat_database to increment?

2019-12-04 Thread Hu, Patricia
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

Re: what causes xact_rollback for a database in pg_stat_database to increment?

2019-12-04 Thread Justin
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 emit

Re: what causes xact_rollback for a database in pg_stat_database to increment?

2019-12-04 Thread Jerry Sievers
"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 confir

Logging

2019-12-04 Thread Rich Shepard
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

Re: Logging

2019-12-04 Thread Stephen Eilert
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 /v

Re: Logging

2019-12-04 Thread Adrian Klaver
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 postgresq

Re: Logging [RESOLVED]

2019-12-04 Thread Rich Shepard
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 ba

Re: Logging

2019-12-04 Thread Rich Shepard
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