Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Using the approach above I was able to query data, but with out the indexes my queries took more then 40 seconds. –  Moe Be Nov 5 '12 at 19:55
    
Did you consider copying the entire instance and then dropping in the new cluster the databases you don't want? –  Daniel Vérité Nov 6 '12 at 11:35

1 Answer 1

If you want to copy a complete PostgreSQL database within its cluster, the fastest method is to use it as TEMPLATE in a CREATE DATABASE statement. I quote the manual:

By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, by writing TEMPLATE template0, you can create a virgin database containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.

CREATE DATABASE db_copy TEMPLATE db_org;

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):

VACUUM FULL ANALYZE;
share|improve this answer
    
The template database would have be created first... So, yes I would not have to reindex but I would still have to create the template database initially which would require generating the index. –  Moe Be Nov 6 '12 at 16:27
    
The goal is to one time copy a database including indexes on a file level between stopped instances of the same version of PostgreSQL. This database is intended to be read only, it is not going to be updated or edited by any clients once deployed. If this process were remotely possible, it would require editing the postgres system database to update OIDs and other information pertinent to the database. –  Moe Be Nov 6 '12 at 16:34
    
@MoeBe: To be clear, any database in the cluster can serve as template. Since you want to copy a database the original has to exist already. Of course, the cluster can't be in a stopped state, but has to be up and running. –  Erwin Brandstetter Nov 6 '12 at 20:56
    
Thanks for the tip :-) But my original question wasn't really specific to a cluster. –  Moe Be Nov 7 '12 at 21:22
    
@MoeBe: You may want to clarify the question. "Instances" could be databases, clusters, installations, ... –  Erwin Brandstetter Nov 8 '12 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.