pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump/pg_restore question
3+ messages / 3 participants
[nested] [flat]
* pg_dump/pg_restore question
@ 2004-08-23 02:59 Worik <worik@noggon.com>
0 siblings, 2 replies; 3+ messages in thread
From: Worik @ 2004-08-23 02:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump/pg_restore question
@ 2004-08-23 04:57 Iain <iain@mst.co.jp>
parent: Worik <worik@noggon.com>
1 sibling, 0 replies; 3+ messages in thread
From: Iain @ 2004-08-23 04:57 UTC (permalink / raw)
To: worik@noggon.com; 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_dump/pg_restore question
@ 2004-08-23 07:56 Richard Huxton <dev@archonet.com>
parent: Worik <worik@noggon.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-08-23 07:56 UTC (permalink / raw)
To: worik@noggon.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-23 07:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-23 02:59 pg_dump/pg_restore question Worik <worik@noggon.com>
2004-08-23 04:57 ` Iain <iain@mst.co.jp>
2004-08-23 07:56 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox