Обсуждение: restore a dump db from tar file
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
Question
how can i restore it now ?
could something like that could work on Windows XP ?
createdb sewe
gunzip -c sewe.tar | psql sewe
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
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
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
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.
but i get the following error message:
where raf_new is my computer user and not user i used in my pg_restore :-(
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
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:AAIU, the custom dump formats are less portable than the plain-text
> 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
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:Just a guess, since I haven't used pg_restore - a quick look at the docs
> this is basically what i use but it does not work.
>
> pg_restore -C -d sewe survey.tar -U postgres
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.