Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I make a duplicate copy of a postgresql database?

Some (I think) steps would be:

  • stop all queries
  • stop the postgresql daemon
  • copy the database files to a new location
  • make some changes to the configuration files
  • reload the server

Any ideas what should I change in the postgresql.conf in order to inform the postgresql server about the new database?

share|improve this question

Copying files will not work.

Just pg_dump the database, and load it using pg_restore or psql (depending on options given to pg_dump).

share|improve this answer
    
How can I rename it? – Adam Matan Aug 30 '09 at 19:47
    
Rename what? You dump with pg_dump MYBASE > MYBASE.sql, you create the duplicate with createdb MYBASE2, you populate it with psql -f MYBASE.sql MYBASE2. – bortzmeyer Sep 3 '09 at 9:22

You have to correct all the absolute paths (if any) in the config files and the PGDATA variable (or option) in the startup script.

share|improve this answer

As @user13185, you should use pg_dump / pg_restore.

But it's a good practice to rename your database prior to start the dump.

In order to do that, all connections to that database should be disconnected.

share|improve this answer

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.