Postgresql – Moving postresql 9.1 database – live

linuxpostgresqlpostgresql-9.1Ubuntu

This question has probably been asked and answered before, and if so, I would appreciate if that could be pointed out to me.

I have googled this point quite a bit, but I want to be very sure as to what I am doing before performing this task:

I need to move my current postresql 9.1 database to a new server. This new server will be taking over on that account and the other "turned off".

I need to do this live without, as much as possible, disturbing the users or having to shut down operation.

I also need to move without any possibility of data-loss (anything being written during the move).

Like I said above, if this is already answered/laid out anywhere, I would much appreciate being pointed to it.

Note:

I am currently running on Gentoo Linux and will be moving to a Ubuntu server.

Best Answer

If the PostgreSQL version and architecture is the same on the target so they're binary compatible: Set up streaming replication with WAL archive fallback, so the new host replicates from the old one. Enable hot_standby mode so you can connect to the new host read-only. Test. Then when you're ready to cut over, stop the old server and pg_ctl promote the new server to a read/write master.

If the PostgreSQL version is different on the target: Set up Londiste or Slony-I logical replication from the old DB to the new one. Let it sync up. When ready to switch over, shut down the old server, stop the replication service, switch clients to write to the new server.

The original answer

Please go back in the logs to before the restart and see if you can find any errors. WAL corruption absolutely should not happen, so if it has it's important to look into why. If you can upload a copy of the whole log to a pastebin or something that'd be really handy.

The only time where WAL corruption is an accepted possibility with PostgreSQL is if you are running with fsync=off set in PostgreSQL.conf and your system crashes or unexpectedly loses power. If that's not the cause, it'd be really good to look into what happened.

Please do not use pg_resetxlog without some idea why your xlogs are damaged. If the transaction logs become damaged something is badly wrong and you need to find out what. If you band-aid it now, you might be bitten by it later when you care about the data.

The transaction logs exist for a reason and just removing them can leave your tables and indexes in an inconsistent, damaged state. After a pg_resetxlog it's a very good idea to pg_dumpall, drop your cluster, re-initdb, and reload the DB. As I said, though, this should not happen and you should look back in the logs for clues about what could've happened.

Now read the comments

Postgresql – Unable to disable SQL statement logging in PostgreSQL 9.1

There are a number of relevant settings missing in your list. Check the chapter "When to log" in the manual, in particular these settings:

log_min_messages (enum)
log_min_error_statement (enum)
log_min_duration_statement (integer) -- as @Jack already hinted in his comment.

Best Answer

Related Solutions

Postgresql – Recover PostgreSQL database from WAL errors on startup

The original answer

Now read the comments

Postgresql – Unable to disable SQL statement logging in PostgreSQL 9.1

Related Question