[GENERAL] Taking rsynced base-backup without wal-archiving enabled
Hi all, Can I do a consistent file-system-level backup using the following procedure: 1) SELECT pg_start_backup(...) 2) rsync postgres data dir to another server 3) SELECT pg_stop_backup() 4) rsync pg_xlog directory From what I understand this should be similar to running pg_basebackup using the -x parameter, correct? One caveat seems to be that wal_keep_segments should be set high enough. Can anybody tell what high enough usually is? Thanks in advance for you help! Best regards, Juergen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LDAP authentication not working
Original Message Subject: Re: [GENERAL] LDAP authentication not working Resent-From: juergen.fuchsber...@uni-graz.at Date: Wed, 14 May 2014 06:47:45 -1000 From: Stephan Fabel sfa...@hawaii.edu To: Magnus Hagander mag...@hagander.net CC: Postgres List pgsql-general@postgresql.org, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at On May 14, 2014 12:56 AM, Magnus Hagander mag...@hagander.net mailto:mag...@hagander.net wrote: On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at mailto:juergen.fuchsber...@uni-graz.at wrote: On 05/14/2014 09:10 AM, Magnus Hagander wrote: On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu mailto:sfa...@hawaii.edu mailto:sfa...@hawaii.edu mailto:sfa...@hawaii.edu wrote: I don't think SSL support for LDAP is supported. Have you tried TLS on port 389? This does not work with our LDAP server (seems it is not configured to support TLS) Enabling TLS on OpenLDAP is trivial, especially if you have SSL enabled already. Ask your SysAdmin. I did - as far as he knows we can not use both at the same time. Juergen. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LDAP authentication not working
Hi, I'm running postgresql 9.1 on Debian and am trying to set up LDAP authentication using the following configuration in pg_hba.conf: hostssl testdb all 143.50.203.0/24 ldap ldapserver=wegc24.uni-graz.at ldapport=636 ldapbinddn=cn=nss,dc=uni-graz,dc=at ldapbindpasswd=thepasswd ldapbasedn=dc=uni-graz,dc=at Trying to access testdb via psql fails with the following error in the log: '''could not perform initial LDAP bind for ldapbinddn cn=nss,dc=uni-graz,dc=at on server wegc24.uni-graz.at: error code -1''' Unfortunately I did not find what error code -1 means. Ldapsearch works fine: ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D CN=nss,DC=uni-graz,DC=at Interesting is also, that postgres seems to not even reach the ldap server: If I change parameter ldapserver to a non-existing url it gives the same error code -1. Any help much appreciated! Best, Juergen signature.asc Description: OpenPGP digital signature
Re: [GENERAL] File system level backup of shut down standby does not work?
All, One very important thing I just noted when shutting down and restarting my standby server: My standby server *always needs the last WAL-file* from the archive directory, even when the shut down was smart. Without this the consistent recovery state will not be reached. 2014-02-19 11:10:20 CET LOG: received smart shutdown request 2014-02-19 11:10:20 CET LOG: shutting down 2014-02-19 11:10:20 CET LOG: database system is shut down 2014-02-19 11:11:00 CET LOG: database system was shut down in recovery at 2014- 02-19 11:10:20 CET 2014-02-19 11:11:00 CET LOG: entering standby mode 2014-02-19 11:11:00 CET LOG: incomplete startup packet 2014-02-19 11:11:01 CET FATAL: the database system is starting up *2014-02-19 11:11:01 CET LOG: restored log file* *000102DE00BF from archive* 2014-02-19 11:11:01 CET LOG: redo starts at 2DE/BF036FA4 2014-02-19 11:11:01 CET FATAL: the database system is starting up 2014-02-19 11:11:01 CET LOG: consistent recovery state reached at 2DE/BFFFE53C 2014-02-19 11:11:01 CET LOG: database system is ready to accept read only connections So my question is, could there be something wrong with my configuration or is this normal? Juergen On 02/19/2014 02:14 AM, Antman, Jason (CMG-Atlanta) wrote: Juergen, I've seen this quite a lot in the past, as we do this multiple times a day. Here's the procedure we use to prevent it: 1) read the PID from postmaster.pid in the data directory 2) Issue service postgresql-9.0 stop (this does a fast shutdown with -t 600) 3) loop until the PID is no longer running, or a timeout is exceeded (in which case we error out) 4) the IMPORTANT part: `pg_controldata /path/to/data | grep Database cluster state: *shut down` - if pg_controldata output doesn't include shut down or shut down in recovery, then something's amiss and the backup won't be clean (error in shutdown, etc.) 5) `sync` 6) now take the backup -Jason On 02/17/2014 08:32 AM, Jürgen Fuchsberger wrote: Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (Log-shipping standby as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html) I take frequent backups of the standby server: 1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server. I just tried to recover one of these backups which *failed* with the following errors: 2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30061 (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30060 (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery? Best, Juergen -- | Jürgen Fuchsberger, M.Sc. | Wegener Center for Climate and Global Change | University of Graz | Brandhofgasse 5, A-8010 Graz, Austria | phone: +43-316-380-8438 | web: www.wegcenter.at/wegenernet |www.wegenernet.org signature.asc Description: OpenPGP digital signature
Re: [GENERAL] File system level backup of shut down standby does not work?
On 02/19/2014 11:43 AM, Albe Laurenz wrote: Jürgen Fuchsberger wrote: One very important thing I just noted when shutting down and restarting my standby server: My standby server *always needs the last WAL-file* from the archive directory, even when the shut down was smart. Without this the consistent recovery state will not be reached. 2014-02-19 11:10:20 CET LOG: received smart shutdown request 2014-02-19 11:10:20 CET LOG: shutting down 2014-02-19 11:10:20 CET LOG: database system is shut down 2014-02-19 11:11:00 CET LOG: database system was shut down in recovery at 2014- 02-19 11:10:20 CET 2014-02-19 11:11:00 CET LOG: entering standby mode 2014-02-19 11:11:00 CET LOG: incomplete startup packet 2014-02-19 11:11:01 CET FATAL: the database system is starting up *2014-02-19 11:11:01 CET LOG: restored log file* *000102DE00BF from archive* 2014-02-19 11:11:01 CET LOG: redo starts at 2DE/BF036FA4 2014-02-19 11:11:01 CET FATAL: the database system is starting up 2014-02-19 11:11:01 CET LOG: consistent recovery state reached at 2DE/BFFFE53C 2014-02-19 11:11:01 CET LOG: database system is ready to accept read only connections So my question is, could there be something wrong with my configuration or is this normal? As far as I can tell, that is as expected: Upon restart, the recovery process will continue from the last restart point. So if the restart point happened after replay of WAL record X in WAL file Y, the recovery process will need WAL file Y again to replay X+1 and the following records. Yours, Laurenz Albe OK, so it is important to back up the WAL archive dir too to have a consistent backup. This was not that clear to me and https://wiki.postgresql.org/wiki/Incrementally_Updated_Backups does not mention the WAL archive dir neither. I might update the Wiki entry. Best, Juergen signature.asc Description: OpenPGP digital signature
Re: [GENERAL] File system level backup of shut down standby does not work?
On 02/19/2014 02:14 AM, Antman, Jason (CMG-Atlanta) wrote: Juergen, I've seen this quite a lot in the past, as we do this multiple times a day. Here's the procedure we use to prevent it: 1) read the PID from postmaster.pid in the data directory 2) Issue service postgresql-9.0 stop (this does a fast shutdown with -t 600) 3) loop until the PID is no longer running, or a timeout is exceeded (in which case we error out) 4) the IMPORTANT part: `pg_controldata /path/to/data | grep Database cluster state: *shut down` - if pg_controldata output doesn't include shut down or shut down in recovery, then something's amiss and the backup won't be clean (error in shutdown, etc.) 5) `sync` 6) now take the backup -Jason Jason, Thanks a lot! That's exactly what I need. Juergen. On 02/17/2014 08:32 AM, Jürgen Fuchsberger wrote: Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (Log-shipping standby as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html) I take frequent backups of the standby server: 1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server. I just tried to recover one of these backups which *failed* with the following errors: 2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30061 (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30060 (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery? Best, Juergen signature.asc Description: OpenPGP digital signature
[GENERAL] File system level backup of shut down standby does not work?
Hi all, I have a master-slave configuration running the master with WAL archiving enabled and the slave in recovery mode reading back the WAL files from the master (Log-shipping standby as described in http://www.postgresql.org/docs/9.1/static/warm-standby.html) I take frequent backups of the standby server: 1) Stop standby server (fast shutdown). 2) Rsync to another fileserver 3) Start standby server. I just tried to recover one of these backups which *failed* with the following errors: 2014-02-17 14:27:28 CET LOG: incomplete startup packet 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery at 2013-12-25 18:00:03 CET 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30061 (log file 483, segment 97): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record 2014-02-17 14:27:28 CET LOG: could not open file pg_xlog/000101E30060 (log file 483, segment 96): No such file or directory 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:29 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:30 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:31 CET FATAL: the database system is starting up 2014-02-17 14:27:32 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET FATAL: the database system is starting up 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated by signal 6: Aborted 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process failure So it seems the server is missing some WAL files which are not in the backup? Or is it simply not possible to take a backup of a standby server in recovery? Best, Juergen signature.asc Description: OpenPGP digital signature
[GENERAL] Corrupt Incrementally Updated Backup: missing pg_clog file
Hi all, I have a problem with a corrupt backup, fortunately I was only testing so I did not loose any data. Unfortunetely what I did is to follow the backup guidelines in the documentation, which I thought should work reliably. Here are the details: I am running a postgreSQL 8.4 database on a Debian Squeeze system. For Backups I am using the warm standby and Incrementally Updated Backup method as described in chapter 24.4 of the documentation. So my Setup is as follows: Server 1 (Main): PostgreSQL 8.4 Database with archive_mode enabled shipping WAL files to a NFS drive. Size of database is about 370 GB and growing. Server 2 (Replica): PostgreSQL 8.4 Database in recovery mode. Using pg_standby in recovery.conf and getting WAL files from Server 1 NFS drive. All this works fine and runs without errors. The replica is backed up once a week using rsync, a full backup runs about 10 hours, so I also keep at least 24h of WAL files to make sure I have a consistent backup. The backup process also runs fine without errors, only the time (10h) it takes is quite long, so I decided to test the backup: 1) Restored the full backup to a test directory (var/lib/postgresql/8.4/test) 2) Copied the configuration of the main server to /etc/postgresql/8.4/test/ Altered port number, paths and turned off archive mode in postgresql.conf. 3) Added a recovery.conf in the test servers data dir. Recovering from my backed up WAL files: restore_command = 'cp /var/postgresql-wal-test/%f %p' 4) Started the test server (pg_ctlcluster 8.4 test start) 5) Waited until recovery was done (everything worked fine until then) 2012-09-25 08:26:41 UTC LOG: database system is ready to accept connections 2012-09-25 08:26:41 UTC LOG: autovacuum launcher started 6) Connected via psql to the database and tried a \d to see my tables which did *not* work! Here is the output: 2012-09-25 08:27:03 UTC ERROR: could not access status of transaction 500185903 2012-09-25 08:27:03 UTC DETAIL: Could not open file pg_clog/01DD: No such file or directory. Also trying to SELECT data from the database tables failed with the same error. The backup is corrupt. So my question is, what went wrong: Obviously as the rsync started it copied everything from the pg_clog (which at this point was until pg_clog/01DC) and then went on for another 10+ hours backing up all the rest of the database. At the time the backup ended, the database content changed but the newer clog files did not go into the backup. When restoring the backup and starting the server, the recovery process started at a point where pg_clog was at state 01DE or even further and thus the data from 01DD was missing. So what I do from now, is an extra daily backup of my clog directory to make sure to have working backups. This is not documented in the postgreSQL documentation, and since the result in not doing so can be quite severe I think you should consider this in future PostgreSQL documentation versions. Regards, Juergen Additional information: The EXACT PostgreSQL version you are running: PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit How you installed PostgreSQL From Linux distro package management: Debian/Aptitude If so, what repository? deb http://ftp.at.debian.org/debian/ squeeze main non-free contrib deb-src http://ftp.at.debian.org/debian/ squeeze main non-free contrib deb http://security.debian.org/ squeeze/updates main contrib non-free deb-src http://security.debian.org/ squeeze/updates main contrib non-free deb http://ftp.debian.org/debian squeeze-updates main contrib non-free deb-src http://ftp.debian.org/debian squeeze-updates main contrib non-free Changes made to the settings in the postgresql.conf file: name | current_setting --+- version | PostgreSQL 8.4.13 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 32-bit archive_command | cp -i %p /var/postgres-wal/%f /dev/null cp -i %p /var/postgres-wal/bak/%f /dev/null gzip /var/postgres-wal/bak/%f archive_mode | on archive_timeout | 0 client_encoding | utf8 effective_cache_size | 1000MB lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 listen_addresses | * log_line_prefix | %t maintenance_work_mem | 256MB max_connections | 100 max_stack_depth | 2MB password_encryption | on port | 5432 server_encoding | UTF8 shared_buffers | 650MB ssl | on synchronous_commit | off TimeZone | UTC work_mem | 40MB Operating system and version Linux distro and version: Debian 6.0.6 (squeeze) Kernel details: Linux wegc203094 2.6.32-5-686 #1 SMP Sun May 6 04:01:19 UTC 2012 i686 GNU/Linux What program you're using to connect to PostgreSQL: psql and
