Postgresql – Slony Replication: Will data eventually be 100% replicated if daemon dies for long period

postgresqlslony

I am using Slony to replicate a Postgres database.

If my slony daemon exits for whatever reason, and doesn't start up hypothetically until 1 month later, will all the data that has accumulated in the master be replicated eventually to the slave? Of course, it might take a long time for that to happen, but will any data be lost as a result?

Best Answer

This is a fairly expert-level question and I could be wrong, but I think you are going to have a lot of issues in the scenario you suggest.

The basic issue is that while Slony is designed to handle periods of inactivity, you have a lot of stuff that is going to be queued for replication. If you make any DDL queries in the mean time, those can't get queued, and slonyk will not work to replicate them, so you will have a significant number of problems here.

My recommendation is not to use Slony for things where you expect long periods of inactivity (say, more than a couple of days). The limitations you will run into keeping this running are significant.

So yes, your data will eventually get replicated, maybe.... It depends on whether other problems surface in the mean time. I wouldn't recommend it in that case.

Related Solutions

Postgresql – Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving

Yes, it will catch up, using streaming only, if (and only if), the number of WAL segments generated since the last update on the standby is less than the value of wal_keep_segments in postgresql.conf. This is covered in this section of the documentation: Replication

Postgresql – Postgres replication/replication to server outside firewall

You can do this with the log-shipping standby functions, which is really an extension of continuous archiving.

You first configure PostgreSQL to write all* changes to the Write-Ahead Log by saying:

wal_level = hot_standby
archive_mode = on

Once a log file is full (16 MB of writes) or a configurable timeout occurs (archive_timeout), PostgreSQL starts writing to the next log file, and it calls your archive_command with the filename of the log it just finished writing. Your archive_command would be a script that uses some other appropriate technology to push this log file off the master -- maybe you want to upload it to an object store like Amazon S3, or maybe you want to use SCP to push it straight onto the slave, or maybe you want to do something else entirely.

You'd set up the slave similarly to the master, except its data directory would include a recovery.conf saying standby_mode = on. If the master copies logs straight into the slaves' pg_xlog directory, you're done; if not, the slave will need a restore_command script to act as an inverse of archive_command and retrieve the indicated archive log from wherever you put it.

Lastly, copy over a base backup (as described in the continuous archiving documentation), start PostgreSQL, and your slave will be following the master without needing any direct communication.

Best Answer

Related Solutions

Postgresql – Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving

Postgresql – Postgres replication/replication to server outside firewall

Related Question