How do you copy a single Postgres database between installations without using pg_dump and without regenerating indexes?
We have an application we deploy which uses Postgres 8.4.7 database in Linux. We have a rather large static data set (4GB) which is is indexed.
Using pg_dump we have to index the data once it is restored to the second Postgres instance. Indexing this data can take up to 80 minutes, so we would rather deploy the database with the index already generated.
From postgres documentation it appears that databases are contained in sub-directories under postgresql/8.4/main/base based on their OID.
My first attempt was to create an empty database in Postgres, shutdown the instance, then copy the data from the existing instance to the directory of the OID for the empty database. This failed as my indexes were not working. Preforming a vacuum gave me the following error:
WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_type" page 6
WARNING: PD_ALL_VISIBLE flag was incorrectly set in relation "pg_depend" page 39
ERROR: xlog flush request 0/8B199518 is not satisfied --- flushed only to 0/8AF81680
CONTEXT: writing block 39 of relation base/17004/2608
Best Answer
If you want to copy a complete PostgreSQL database within its cluster, the fastest method is to use it as
TEMPLATE
in aCREATE DATABASE
statement. I quote the manual:This effectively copies underlying files around like you tried manually. Except that is sets everything up to work correctly.
You may want to clean up your original before you do (depends):