I'm trying to replicate a database with pg_basebackup in order to use it as standby server. But when I'm trying to start up PostgreSQL I get the following errors and the server shuts down again:

2016-08-09 15:53:38 CEST [10369]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10372]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:39 CEST [10375]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10378]: [1-1] [postgres: [local]] FATAL:  the database system is starting up
2016-08-09 15:53:40 CEST [10361]: [4-1] [: ] LOG:  redo starts at 11/188CBA38
2016-08-09 15:53:40 CEST [10361]: [5-1] [: ] FATAL:  could not access status of transaction 4809883
2016-08-09 15:53:40 CEST [10361]: [6-1] [: ] DETAIL:  Could not read from file "pg_clog/0004" at offset 147456: Success.
2016-08-09 15:53:40 CEST [10361]: [7-1] [: ] CONTEXT:  xlog redo Transaction/COMMIT: 2016-08-09 15:41:25.947996+02
2016-08-09 15:53:40 CEST [10359]: [3-1] [: ] LOG:  startup process (PID 10361) exited with exit code 1

I call pg_basebackup like this

sudo -u postgres pg_basebackup -h [server] -D [dbdir] -U replicator -v -P

My recovery.conf Looks like this

standby_mode = 'on'
primary_conninfo = 'host=[host] port=[port] user=replicator password=[password] sslmode=require'
trigger_file = '/tmp/postgresql.trigger'

When I'm trying to follow this solution I'm getting the error message

ERROR:  requested WAL segment 0000000100000010000000EF has already been removed

how can I avoid the broken clog file in the first place?

i'm using PostgreSQL 9.5.3

| improve this question | | | | |
  • Have you read Simon Riggs's answer on that post? Zeroing the clog out is not very wise (usually). OTOH, how are you copying the files? And have you set up WAL archiving or a replication slot? – dezso Aug 9 '16 at 16:27
  • i know i shouldnt do it, just did to see if the server would startup when this problem is fixed. thats why i'm seeking advise how to avoid this Situation. I'm not copying the files but i use pg_basebackup. i thought postgres (in standby mode) would request the WAL files that have been created during and after pg_basebackup – RootOfProblem Aug 10 '16 at 6:36

This probably happens because the master doesn't keep the wal segements long enough so they are deleted before your base backup completes. You can use the option --xlog-method=stream with pg_basebackup to tell it to also stream the wal while making the backup. This way you will have all the wall segments the backup needs to start succesfully.

| improve this answer | | | | |
  • I discovered that the broken Clog came from a malfunctioning fireall which for some reason interfered with the data Transfer. after doing it again without the firewall the clog was correct. – RootOfProblem Aug 15 '16 at 18:45
  • i also copied all WAL files from the source database to the new Server even thow i used the -x Option on pg_basebackup. I dunno why the WAL File wasnt avail for the Standby-Server. The missing WAL file wasn't one created during the basebackup – RootOfProblem Aug 15 '16 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.