Postgresql – Merging Postgresql dump with production database

postgresql

I have a postgresql database that lost some records. I want to take a pg_dump SQL dump of the database from a few days ago and merge it with the current production database. A lot of the records are going to be duplicates so it can skip the duplicates. What is the best way to do this?

Best Answer

patch could be you friend could try creating a new dump, we'll call it current_dump and do a diff against old_dump then use

   diff old_dump new_dump > patch.input
    (copy the new_dump) to another file at this point new.db.dump)
    patch new.db.dump patch.input

You might have to sort the sort the dump files before you do the diff and depending on how big your database is this might not really be practical.

Also be very careful and do this against a backup until you are sure its right.

Otherwise, maybe there are ETL type tools that can do this.

Related Solutions

Postgresql – Export and import a PostgreSQL database with a different name

The solution was dumping it like this:

pg_dump --no-owner --no-acl blah > blah.psql

and importing it like this:

psql blah_devel < blah.psql > /dev/null

I still get this warning:

WARNING:  database "blah" does not exist

but the rest seems to work.

PostgreSQL EC2 Backup Strategy

I can't speak for PostgreSQL as I don't use it, but I use a variation on your option 1 for backing up MySQL databases on EC2, and have successfully restored them, without issue.

The first requirement, of course, is that your databases are stored on an EBS volume so that they can be snapshotted. I favour using XFS as the filesystem since the entire filesystem can easily be frozen.

To begin the snapshotting process, you want to freeze your databases, and flush your tables. There is a great script that will do this, as well as freezing your filesystem (if xfs) called ec2-consistent-snapshot (the site does have some comments on PostgreSQL that may point in your an acceptable direction - it is designed for Ubuntu, but, works on other distributions (e.g. Amazon's Linux/CentOS) without much issue). My understanding is that with PostgreSQL, people often simply take the snapshot (after freezing the file system) and rely on PostgreSQL's built-in recovery abilities to restore everything to a functioning state. The xfs_freeze, is still important to get a consistent snapshot though.

Once your file system is frozen (and your database flushed and locked if possible), take your snapshot (ideally, use the API directly, as opposed to the (very) slow Java based commands). The snapshot command only takes a (few) second(s) to return, after which you can unfreeze the file system - the snapshot created will be consistent, despite additional reads.

Given that snapshots are differential (in a way) and compressed, this approach is much more economical than using S3, as well as offering more options, it also allows you to restore data far faster, and should lock up your databases for a shorter period than generating a dump. It is also possible to rotate your snapshots to keep their numbers under control - I wrote a perl script to do that for me.

If in doubt, try the first option, and then create an EBS volume and test the database to see that all works - don't just trust that the backup is good.

Best Answer

Related Solutions

Postgresql – Export and import a PostgreSQL database with a different name

PostgreSQL EC2 Backup Strategy

Related Topic