PostgreSQL hot_standby pg_dump on slave

backuppg-dumppostgresqlreplication

I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump.

I switched to postgres from MySQL. With MySQL replication I made backups from slave with these steps:

stop slave;
perform mysqldump
start slave

Can I do something similar with postgres?

I tried pg_dump on slave, but it returned errors:

pg_dump: Dumping the contents of table "news_event" failed:   PQgetResult() failed.
pg_dump: Error message from server: ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Also, pg_dump works good on master so I can do backups on master server, but I prefer to make it on slave.

Best Answer

Just run pg_dump. The documentation says:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

In turn, the state of the database that you see on the slave is also consistent. If there is a considerable delay in replication, the result may be slightly different than a dump from the master.

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

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?

Postgresql – How to monitor PostgreSQL WAL shipping if I’m not using streaming replication

Here are a few ways:

Compare pg_current_xlog_location() on the primary with pg_last_xlog_replay_location() on the standby. That will give you the lag in bytes, which might not be very useful for alerting, but it can be useful to chart it.
Monitor pg_last_xact_replay_timestamp() on the standby against the current time.
Have a cron job on the primary periodically modify a value and then check how long it takes to get to the standby. That's essentially how it used to be done before pg_last_xact_replay_timestamp() became available.

Best Answer

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

Postgresql – How to monitor PostgreSQL WAL shipping if I’m not using streaming replication

Related Question