How to Import Old pgdump into Updated Schema

backuppostgresqlpsql

I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought…

The Plan was to use our backup, created via

pg_dump -U derp -h db-host -Fc derp > backup

Flash a clean VM with an upgraded software, with its fancy new schema and use

pg_restore -v -U derp -n public -c -1 -h db-host -d derp backup

to load it with all its fancy old data.

Unfortunately I'm getting errors because of the updated schema, with new columns that create new relations. "cannot drop constrain X on table Y because other objects depend on it"

I tried using -a (data only) but that fails fast because there are many lookup tables with now duplicated keys.

Can I somehow import the existing copy?

If not, can I alter the new database schema in a way that will allow my old dump to work?

If not…. can I make a different, more useful copy of the old database?

If I'm asking really dumb questions, what should I be asking?

Best Answer

It sounds you are dealing with some 3rd party's application. In other words, the app vendor and your predecessor are not the same person.

The 3rd party would generally provide you with two things, an installer to use for new customers, and an upgrade process to upgrade the schema between versions of their app for existing customers. You either need to use one, or the other, not some bastardized combination between them. Restoring a dump of the old schema+data over the top of a newly-initialized schema for new customers would not be expected to work, unless the vendor said it does.

The vendor might also provide instructions on how to upgrade your PostgreSQL version, either by itself or coupled with upgrading the application version. If they do, then find and follow those instructions. If they don't then you have a few options.

Go back to using the old database. Run the upgrade scripts against it. Worry about migrating that to a new database server later.
Create an empty database cluster (completely empty, not using the vendor's new-customer initialization stuff) and restore to that database cluster. The easiest way to do that is probably to use the "-C" (note upper case) flag to pg_restore. This will create the new database for you, with the name, encoding, etc. which was recorded in the dmp file. Alternatively, perhaps you just use your existing cluster (initialized with the app vendors new-customer schema) and add the -C flag to your restore. What that will do is drop the database they provided in-bulk (which will get around the dependency problems) and recreate it from what is in the dump file, meaning you get the old schema not the new one. Either way, you will now have the old application schema running in a new PostgreSQL database. Once this is working, then run the application upgrade script against this.
Ask the 3rd party app vendor what to do.

Related Solutions

Sql-server – Thought about this SQL Server backup plan

While this is functional, there's some holes in here that will make it difficult for to recover in the event of a problem:

I'm not sure what the device is your backing up to for your logs, but based on what I'm seeing here, you're appending all your log backups to the same file/device for all your log backup actions. This can be cumbersome to manage and can cause you storage issues.
The BACKUP LOG ... WITH TRUNCATE_ONLY is redundant and can possibly cause you data loss. Your log truncation occurs after every log backup, so that should be sufficient to manage your log, though you should consider scheduling your log backups for every hour 24/7 instead of just 6 AM-11 PM.
Your full backup with it's current syntax will overwrite your previous backup every run, so unless your saving that backup off to tape between the full backup runs, you will not be able to restore previous to your last full backup.
Shrinking databases regularly is VERY bad practice, for a variety of reasons. Paul Randal talks more about it here.

In general, it looks like your backup/maintenance plan is as follows:

Execute a full backup nightly (1 AM)
Execute transaction log backups every hour
Rebuild fragmented indexes nightly

This is a solid enough plan, it looks like you just need to fill in some of the gaps. Unfortunately, backup/restore is far to large a topic to cover here. I recommend you read about this at SQLServerPedia(now ToadWorld). Since you're committed to SQL 2000, this limits you to making use of some of the currently existing tools out there. However, plenty of code examples exist to fine tune and polish your currently existing code.

As for index fragmentation, without seeing your code, I can't comment to any possible issues with it. If it's been working fine up until now, there's probably no issues there, but you can read more about managing this up on MSDN.

Postgresql – How to request a flush of the postgresql transaction logs

Most likely what you're seeing is a huge checkpoint_segments value and long checkpoint_timeout; alternately, they might have set wal_keep_segments to a very large value if it's supposed to support streaming replication.

You can force a checkpoint with the CHECKPOINT command. This may stall the database for some time if it has accumulated a huge amount of WAL and hasn't been background-writing it. If checkpoint_completion_target is low (less than 0.8 or 0.9) then there's likely to be a big backlog of work to do at checkpoint time. Be prepared for the database to become slow and unresponsive during the checkpoint. You cannot abort a checkpoint once it begins by normal means; you can crash the database and restart it, but that just puts you back to where you were.

I'm not certain, but I have the feeling a checkpoint could also result in growth of the main database - and do so before any space is freed in the WAL, if it is at all. So a checkpoint could potentially trigger you running out of space, something that's very hard to recover from without adding more storage at least temporarily.

Now would be a very good time to get a proper backup of the database - use pg_dump -Fc dbname to dump each database, and pg_dumpall --globals-only to dump user definitions etc.

If you can afford the downtime, stop the database and take a file-system level copy of the entire data directory (the folder containing pg_xlog, pg_clog, global, base, etc). Do not do this while the server is running and do not omit any files or folders, they are all important (well, except pg_log, but it's a good idea to keep the text logs anyway).

If you'd like more specific comment on the likely cause (and so I can be more confident in my hypothesis is) you can run the following queries and paste their output into your answer (in a code-indented block) then comment so I'm notified:

SELECT version();

SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default', 'override');

It is possible that setting checkpoint_completion_target = 1 then stopping and restarting the DB might cause it to start aggressively writing out queued up WAL. It won't free any until it does a checkpoint, but you could force one once write activity slows down (as measured with sar, iostat, etc). I have not tested to see if checkpoint_completion_target affects already-written WAL when changed in a restart; consider testing this on a throwaway test PostgreSQL you initdb on another machine first.

Backups have nothing to do with WAL retention and growth; it isn't backup related.

See:

Best Answer

Related Solutions

Sql-server – Thought about this SQL Server backup plan

Postgresql – How to request a flush of the postgresql transaction logs

Related Question