• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1058
  • Last Modified:

Copy Postgres to another computer

I just installed Postgres on a test server and I need some guidance on how to copy a Postgres database and all relevant information from a Windows server to my test Windows server

I know that I would need to do a dump from the source computer and restore it on the target computer, but I'm not sure of the exact commands or any other relevant information that I should also copy over such as the schema.  

Just as an FYI, I have the user name/password for the database root account and for the backup and restore.
0
abgtemp
Asked:
abgtemp
  • 2
1 Solution
 
cminearCommented:
Use 'pg_dump' to dump the database you want.  The simplest invocation would be:
  pg_dump -U <dbuser> --format p -f <outfile> <dbname>

Move the database file to the test system.  To recreate the database on your test system, I would do the following:
  dropdb -U <dbuser> <dbname>
  createdb -U <dbuser> <dbname>
  psql -U <dbuser> -d <dbname> -f <outfile>

Of course, this is probably fairly drastic, but it works just fine for me, when I want a copy of the production database in my test envionrment.  But there are options that can be used with 'pg_dump' to only get the data (or the schema) in the database dump.  In that case, you'd only need to TRUNCATE the database tables.

See http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html for details.

(Note that I'm assuming the Windows version of these programs accept the same switch options as the Unix version.)
0
 
abgtempAuthor Commented:
This sounds like it will work. I will leave this question open for one more day just in case I have anymore questions. I'm also bumping up the point to 400 for the quick response. Thanks!!!
0
 
abgtempAuthor Commented:
Quick and accurate answer!!! Thanks!!!!!!!!!!!!!!!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now