Re: How to restore some DBs to a new server?

On 9/13/2013 2:03 PM, Bob Futrelle wrote:
> Running on my MacBook Pro, pgAdmin3 tells me I have four servers in
> "Server Groups",
> all local, no network involved.
>
> I have dumped two of my DBs from a current server using pg_dump.
> Then in pgAdmin3 I created another server, let's call it "New".
>
> I would like to look at these earlier DBs while not disturbing my
> current DBs.
> So I would like to reload both the dumps into "New",
> but I can't find the magic invocation that does this,
> in pgAdmin3 or using pg_restore or directly in psql.
>
> I realize that this isn't rocket science, so I'll blush a bit when
> the simplicity of it is revealed.

first, dumps don't contain roles or tablespaces as these things are
global to the cluster, so you'll need to first create all the required
roles and tablespaces (if any) on the 'New' server.

you'll need to pre=create the new databases

createdb db1 -O owner1
createdb db2 -O owner2
createdb db3 -O owner3

if your dumps were made in pg_dump -Fc mode, then simply doing...

pg_restore -h newhost -d db1 dumpfile1.dump
pg_restore -h newhost -d db2 dumpfile2.dump
pg_restore -h newhost -d db3 dumpfile3.dump

if they were made without the -Fc, eg are simple sql dumps, then try...

psql -h newhost -d db1 -f dumpfile1.sql
psql -h newhost -d db2 -f dumpfile2.sql
psql -h newhost -d db3 -f dumpfile3.sql

i'm sure you can do the equivalent in pg_admin but its tedious to
describe how to point-click your way through a GUI.

--
john r pierce 37N 122W
somewhere on the middle of the left coast