Postgresql – How to we execute a log file from pg_xlogs directory postgresql

postgresqlUbuntuwrite-ahead-logging

Is it possible to execute a Wal log that is copied from a machines pg_xlogs directory and all the queries inside it are executed on another machine? What command might be used to carry out this action (if there is any command)?

Best Answer

No, it doesn't work like that.

The transaction logs don't contain queries. They contain the changes to disk blocks - heap and indexes - along with records of transaction ID allocations, commits, etc. It's all very low level, and it makes absolutely no sense to try to apply it to another unrelated PostgreSQL instance.

You can use the transaction logs (WAL) for replication, per the PostgreSQL manual.

PostgreSQL also has the beginnings of logical replication functionality. Currently that's mainly accessible via 3rd party tools like pglogical. There are trigger-based alternatives like Londiste too.

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 – EC2 – How to correctly back up PostgreSQL data

See the fine manual. If my advice conflicts with its' in any way, it's right.

A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no checksumming done, so you could be really unlucky and have it try to apply garbage data that by sheer insane chance happened to look like real WAL records. In your position I'd be syncing the volume before a snapshot to make sure any unwritten dirty buffers in RAM hit the file system image on disk. A freeze would help avoid messy but non-fatal partially written WALs, so it's not a terrible idea but not vital. What's vital is to have an undamaged timeline up until the point of recovery. Personally, I write my WALs to a temporary file name and rename them to their final name only once fully copied; if you do this, you don't need to freeze.
Sounds correct. A live snapshot is just like doing a plug pull test on a live system with write-through caching. Your database should recover fine when restored from a live snapshot, same as after plug-pull. I'd recommend that you automate tests of restores from snapshots. (Note: A snapshot restore test is not a complete substitute for plug pull testing because it doesn't account for possible disk, raid controller, etc write caching). Not only the config files and the dump, but the database its self should be fine after your snapshot. Consider syncing the volume before the snapshot to make sure all the dump data etc has actually hit disk.

2a. Might save some disk space. Little difference otherwise. You'll get to keep the snapshots a lot longer without all the churn of the live database on them.
Why even snapshot your code volume? A plain file level copy may well be just fine. Certainly a live snapshot should be.
This is not a solid backup scheme. It fails in one critical area: There is no restore testing and validation being performed. You should always test your backups on a regular basis to make sure you can really restore them.

Personally, I recommend that you use WAL shipping, or send database dumps, to a different host, preferably one not on Amazon EC2 or at least in a different region. This host should perform automated restore tests, send reports to you of the results, and should also be checked manually.

While your snapshots (containing dumps) will be on S3, and will be safe there, that doesn't mean they'll be accessible when you need them urgently. Amazon's durability claims are reassuring, but your data can still be safe and completely inaccessible to you during a badly timed outage of the S3 service.

Best Answer

Related Solutions

Postgresql – Recover PostgreSQL database from WAL errors on startup

The original answer

Now read the comments

Postgresql – EC2 – How to correctly back up PostgreSQL data

Related Question