Thread: 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
------------------------------------------------------------------

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 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
------------------------------------------------------------------