Solved

Copy Postgres to another computer

Posted on 2009-05-15
3
985 Views
Last Modified: 2012-06-27
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
Comment
Question by:abgtemp
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
cminear earned 400 total points
Comment Utility
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
 

Author Comment

by:abgtemp
Comment Utility
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
 

Author Closing Comment

by:abgtemp
Comment Utility
Quick and accurate answer!!! Thanks!!!!!!!!!!!!!!!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now