Thread: pg_dump/pg_restore question

pg_dump/pg_restore question

From
Worik
Date:
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


Re: pg_dump/pg_restore question

From
"Iain"
Date:
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



Re: pg_dump/pg_restore question

From
Richard Huxton
Date:
Worik wrote:
> 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.

Almost correct. You're not actually connecting over IP at all, you'll be 
using unix-sockets. Try a line something like:  local  all  all  trust
Or, since you're on 7.2 probably  local  all  trust

HTH
--   Richard Huxton  Archonet Ltd