1

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:

  1. stop slave;
  2. perform mysqldump
  3. 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.

4

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.

  • I tried it, but it works only on master. On slave I'm getting errors. Is there any ways to fix it? (I updated my question) – Antonio Apr 20 '15 at 8:59
  • 2
    @Antonio: you need to tell us which error you get. If the standby is configured as a "hot standby". You might want to enable the hot_standby_feedback: postgresql.org/docs/current/static/… – a_horse_with_no_name Apr 20 '15 at 10:58
  • @a_horse_with_no_name: error is described in the end of my question. – Antonio Apr 22 '15 at 17:03
1

According to this e-mail thread it appears dumping from slaves requires PostgreSQL v9.1 and with the hot_standby_feedback setting (and possibly other settings) enabled. Hopefully it'll get easier/simpler as replication improves with newer versions.

  • From which version? I cannot figure this out from the thread. You are right with the hot_standby_feedback - without it it may work but there's no guarantee. – dezso Nov 13 '15 at 17:27
  • Earliest reference I found in the documentation is v9.1. Updated answer with that link. – Paul R Rogers Dec 9 '15 at 15:11
0

You can use pgAdmin to perform pg_dump operation and choose what exactly should be in backup file(tables, schemas). Moreover you can choose backup settings such as backup file format. It's very easy to use, if database is big You just wait until process will finish.

To start backup after connecting to PostgreSQL just right click on database and choose Backup... option. Then choose right options for You and start backup process.

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.