Re: pg_dump/pg_restore question - Mailing list pgsql-sql

From Iain
Subject Re: pg_dump/pg_restore question
Date
Msg-id 002e01c488cd$c0089e40$7201a8c0@mst1x5r347kymb
Whole thread Raw
In response to pg_dump/pg_restore question  (Worik <worik@noggon.com>)
List pgsql-sql
Hi Worik,

I can't address the specifics of your problem but I'll give you an example
of I do it on a 7.4 db. I dunno if all options are available on your
version.

To get the backup:

pg_dump --format=c -v DBNAME > FILENAME.dump


To restore you have to create a new DB (drop the old one first if
neccessary):

createdb -T template0  [-E ENCODING] DBNAME

Note that this is template 0 and not template 1. (also, don't forget to set
your database encoding if the default isn't what you want).


Then create a contents list:

pg_restore --list DBNAME.dump > DBNAME.list

You can edit the list file to rearrange the restore order or omit items.


Finally, do the restore:

pg_restore --use-list=DBNAME.list  --dbname=DBNAME -v DBNAME.dump


This is all documented fairly well in the online documentation, you just
need to spend a bit of time going through it. As I recall, some information
is in the admin section and some is in the documentation of the client
programs (pg_dump and pg_restore are client programs iirc).

Also, consider upgrading to 7.4 to ensure you get the best support.

Good luck,
Iain




----- Original Message ----- 
From: "Worik" <worik@noggon.com>
To: <pgsql-sql@postgresql.org>
Sent: Monday, August 23, 2004 11:59 AM
Subject: [SQL] pg_dump/pg_restore question


> Freinds
>
> I am new to this list, and this is my first message.
>
> I hope this is the correct forum, and the question not too stupid/simple.
>
> I have a database on a debian stable system...
>
> dpkg -l postgresql
> [snip]
> ii  postgresql     7.2.1-2woody5  Object-relational SQL database,
> descended fr
>
> When I dump a database (as from the man page for pg_restore)
> pg_dump mydb > mydb.out
>
> OK.  Get an SQL dump in mydb.out
>
> Create a new database....
>
> creatdb myotherdb
>
> Load the dump....
>
> psql -d myotherdb -f mydb.out
> psql:mydb.out:4: \connect: FATAL 1:  IDENT authentication failed for
> user "postgres"
>
> I have tried adding the line
> host       all         127.0.0.1     255.255.255.255    trust
>
> to pg_hba.conf but it makes no difference.
>
> The only thing I can do is edit the dump file into the part that wants
> to connect as postgres and the part that wants to connect as worik and
> run them under their respective logins.
>
> How can I set it up so I do not have to do that?
>
> What documentation should I be reading?
>
> Worik
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings



pgsql-sql by date:

Previous
From: Greg Stark
Date:
Subject: Re: only last records in subgroups
Next
From: Richard Huxton
Date:
Subject: Re: pg_dump/pg_restore question