2

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?

Improve this question

4 Answers 4

Reset to default
5

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

Improve this answer
2
  • How can I rename it?
    – Adam Matan
    Aug 30, 2009 at 19:47
  • 1
    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, 2009 at 9:22
0

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

Improve this answer
0

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.

Improve this answer
0

If you want to move the storage of your data to a new place, copy the data directory to a new place. change the PGDATA viarable in the .service systemd startup file. Then stop the postgres run

postgres initdb

That can bring your postgres to use new directory as the data files including configurations like postgresql.conf

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .