restore a dump db from tar file

2008/10/6 Alain Roger <raf.news@gmail.com>:

Hi,

i backup my database "sewe" using a standard process.
1. it backups only database and not roles :-( roles are backuped separately.
2. backup is a tar file
3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe

You know what all these options mean, do you?
http://www.postgresql.org/docs/8.2/static/app-pgdump.html

Question
how can i restore it now ?

use pg_restore
http://www.postgresql.org/docs/8.2/static/app-pgrestore.html

could something like that could work on Windows XP ?
createdb sewe
gunzip -c sewe.tar | psql sewe

No, it will not work.
Your archive is in "tar" format. gunzip is not needed here.

use something like
pg_restore -d <destination db name> <file name>

HTH

--
Filip Rembiałkowski

Alain Roger wrote:

i backup my database "sewe" using a standard process.
1. it backups only database and not roles :-( roles are backuped separately.
2. backup is a tar file
3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe

Question
how can i restore it now ?
could something like that could work on Windows XP ?
createdb sewe
gunzip -c sewe.tar | psql sewe

Something like
pg_restore -Ft sewe.tar

Yours,
Laurenz Albe

On 06/10/2008 09:07, Alain Roger wrote:

i backup my database "sewe" using a standard process.
1. it backups only database and not roles :-( roles are backuped separately.
2. backup is a tar file
3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe

Question
how can i restore it now ?
could something like that could work on Windows XP ?
createdb sewe
gunzip -c sewe.tar | psql sewe

AAIU, the custom dump formats are less portable than the plain-text
format, so you might be better off using that. In that case, you need to
unzip the file first and then restore it using psql.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

this is basically what i use but it does not work.

pg_restore -C -d sewe survey.tar -U postgres

but i get the following error message:

pg_restore: [archiver (db)] connection to database "sewe" failed: FATAL:

passwo
rd authentication failed for user "raf_new"

where raf_new is my computer user and not user i used in my pg_restore :-(

On Mon, Oct 6, 2008 at 2:07 PM, Raymond O'Donnell <rod@iol.ie> wrote:

On 06/10/2008 09:07, Alain Roger wrote:

i backup my database "sewe" using a standard process.
1. it backups only database and not roles :-( roles are backuped

separately.

2. backup is a tar file
3. backup command is : pg_dump -v -o -U myuser -ci -Ft -f sewe.tar sewe

Question
how can i restore it now ?
could something like that could work on Windows XP ?
createdb sewe
gunzip -c sewe.tar | psql sewe

AAIU, the custom dump formats are less portable than the plain-text
format, so you might be better off using that. In that case, you need to
unzip the file first and then restore it using psql.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008

On 06/10/2008 14:03, Alain Roger wrote:

this is basically what i use but it does not work.

pg_restore -C -d sewe survey.tar -U postgres

Just a guess, since I haven't used pg_restore - a quick look at the docs
suggests that the filename should come *last* on the command line, so
try that.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On Mon, Oct 6, 2008 at 3:12 PM, Raymond O'Donnell <rod@iol.ie> wrote:

On 06/10/2008 14:03, Alain Roger wrote:

this is basically what i use but it does not work.

pg_restore -C -d sewe survey.tar -U postgres

Just a guess, since I haven't used pg_restore - a quick look at the docs
suggests that the filename should come *last* on the command line, so
try that.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

AFAIK, pg_dump -v -o -U username -ci -Ft -f tarname.tar dbname
backup the DB but without inserting code to create the BD itself... just to
create its structure and populate it.
noting about create database dbname
grant. ...

how can i do that automatically ?
i mean when i dump my db, how can i dump ALSO the script to create the BD
and its property ?
thx.

On Mon, Oct 6, 2008 at 9:12 AM, Alain Roger <raf.news@gmail.com> wrote:

AFAIK, pg_dump -v -o -U username -ci -Ft -f tarname.tar dbname
backup the DB but without inserting code to create the BD itself... just to
create its structure and populate it.
noting about create database dbname
grant. ...

how can i do that automatically ?
i mean when i dump my db, how can i dump ALSO the script to create the BD
and its property ?

pg_dumpall produces such output. You could do a schema only
pg_dumpall and grep out the parts you need for that one db.

On 06/10/2008 16:12, Alain Roger wrote:

AFAIK, pg_dump -v -o -U username -ci -Ft -f tarname.tar dbname
backup the DB but without inserting code to create the BD itself... just

What's -ci? Looking at the docs, there's are -c and -i options, but I
don't see -ci.

how can i do that automatically ?
i mean when i dump my db, how can i dump ALSO the script to create the
BD and its property ?

From the docs:

<quote>
-C
--create

Begin the output with a command to create the database itself and
reconnect to the created database. (With a script of this form, it
doesn't matter which database you connect to before running the script.)

This option is only meaningful for the plain-text format. For the
archive formats, you can specify the option when you call pg_restore.
</quote>

So it seems that you can't do it when using a non-text format - you'll
have to do a plain-text dump, and maybe pipe it to tar if you want that.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

From the docs:

<quote>
-C
--create

Begin the output with a command to create the database itself and
reconnect to the created database. (With a script of this form, it
doesn't matter which database you connect to before running the script.)

This option is only meaningful for the plain-text format. For the
archive formats, you can specify the option when you call pg_restore.
</quote>

So it seems that you can't do it when using a non-text format - you'll
have to do a plain-text dump, and maybe pipe it to tar if you want that.

Ray.

Thanks Ray,

this is also what i found later after my post :-(
everything works well now.