16

I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under the pg_log directory reads with no errors. However, when I try to enter into the database using the psql command, I get the error:

FATAL: the database system is starting up.

The recovery.conf file also does not turn to recovery.done. I have extensively researched this error and consistently find the same response: the database has not been cleanly shut down before I tried to restart Postgres. The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database and still receive the same error. I am at a loss of where to go from here and how to fix this issue. Below is the exact process that I have done to complete the hot backup.

Master Server Configurations:

pg_hba.conf, added the line:

host replication postgres IPAddressOfSlaveServer trust

postgresql.conf:

wal_level = hot_standby
max_wal_senders = 5
listen_address = '*'
port = 5432
max_wal_senders = 5
wal_keep_segments = 32

Slave Server Configurations:

postgresql.conf:

hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = host=IPAddressOfMasterServer
port = 5432
user = postgres
restore_command = 'cp /var/lib/pgsql/9.1/data/pg_xlog/%f "%p"'

After configuring both servers

I change to the postgres user on the master server and run the commands:

psql -c "Select pg_start_backup('label',true);";
rsync -a -v -e ssh /var/lib/pgsql/9.1/data slave:/var/lib/pgsql/9.1/data \
        --exclude postmaster.pid
pgsql -c "select pg_stop_backup();";

After syncing database with slave server

I restart the slave server and the start up does not fail. The pgstartup.log reads:

Success. You can now start the database server using:

    /usr/pgsql-9.1/bin/postgres -D /var/lib/pgsql/9.1/data
or
    /usr/pgsql/9.1/bin/pg_ctl -D /var/lib/pgsql/9.1/data -l logfile start

the current day log file, postgresql-Thu.log, reads:

Log: shutting down
Log: Database system is shut down
Log: database system was shut down in recovery at 2012-4-10
Log: entering standby mode
Log: restored log file "logFileName" from archive
Log: consistent recovery state reached at 0/BF0000B0
Log: redo starts at 0/BF000020
Log: restored log file "logFileName" from archive
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: unexpected pageaddr 0/85000000 in log file 0, segment 192, offset 0
Log: streaming replication successfully connected to primary

I researched unexpected pageaddr and from the postgres archives, it is my understanding that it is quite normal and one of the expected ways to detect end-of-WAL.

Any advice would be greatly appreciated.

migrated from stackoverflow.com Apr 19 '12 at 18:36

This question came from our site for professional and enthusiast programmers.

  • tip: Sometimes you just need to wait some minutes for the slave database reach the consistent level after you issue select pg_stop_backup(); on master, and most important dont forget to set hot_standby = on on the slave. – Luciano Andress Martini Mar 13 '18 at 20:07
11

The message "The database system is starting up." does not indicate an error. The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode. On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point? What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there. I'm also concerned about these apparently conflicting statements:

The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database...

Did you try to stop the service through the service script? What happened? It might help in understanding the logs if you prefixed lines with more information. We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd. Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?

7

I had some issues with this as well, except I was on 9.3, not 9.1. Anyway, the fix turned out to be fairly trivial:

The postgresql.conf file was being copied over from the master to the slave, and I was leaving it unmodified on the slave. I thought all you had to do was add a recovery.conf file and everything would work (well it did, but I couldn't log in to the replicated slave server, but, it was being replicated).

I edited the slave's postgresql.conf file and:

  • commented out the archive_mode=on
  • commented out archive command; and
  • commented out hot_standby=on

That did it: I was able to get the database to be a read only server ready to accept read only queries.

There is a script called pg_basebackup that will create the bootstrap directory for the slave. This is the data directory with the database in it. You need to modify the postgresql.conf file before it can be used as the slave as described, something pretty simple for a post pg_basebackup script.

  • 1
    When you write "commented out hot_standby=on" I presume you mean "removed the #-comment-mark before, to actually enable hot_standby" :) If not in hot_standby, the db will always be "starting up" by design (it's warm standby, ready for fail-over, but not querying). Note, that if you made the base-backup dump without having wal_level = hot_standby on the master and then turned on hot_stanby on the slave, you will have to re-dump and re-init the slave db for hot_standby to get up and running. Otherwise you will get some fatal errors. – Frederik Struck-Schøning Mar 21 '18 at 10:16
  • hot_standby=on is required , it must be there – Abhilash Mishra Feb 20 at 0:41
6

Interestingly I solved this the opposite way that Paul did.

I added:

hot_standby = on

or, rather, changed #hot_standby = off to the above. (This was using 9.5)

  • I missed out on what you wrote: thanks so much. It solved my problem. – iChux Nov 30 '16 at 1:29
  • 1
    My postgresql already has this option but the message keep repeating. – Luciano Andress Martini Feb 28 '18 at 20:20
-1

In Postgres 9.5 change /var/postgresql/9.5/main/postgresql.conf

# hot_standby = off
hot_standby = on

Your Answer

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