Обсуждение: pg_restore problem!!!
I made a back up file with this sentence: pg_dump -U [superusername] -C -D -f [filename] [dbname] It seems to work fine. I opened the file and I saw all the commands to insert every piece of data and reconstruct my schema. However when I try to restore that file it gets me the following error: C:\>pg_restore [filename] pg_restore: [archiver] input file does not appear to be a valid archive What I am doing wrong?? Any ideas?? Thank you in advanced. MAGO | |||
IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Вложения
Mago- pg_restore is used to restore a dump file created in one of the non-text formats such as tar format. To restore from a plain-text dump file, just pipe it into psql like so: cat [filename] | psql [dbname] -Nick --------------------------------------------------------------------- Nick Fankhauser nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 doxpop - Court records at your fingertips - http://www.doxpop.com/ -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marvin Sent: Tuesday, July 22, 2003 3:22 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] pg_restore problem!!! I made a back up file with this sentence: pg_dump -U [superusername] -C -D -f [filename] [dbname] It seems to work fine. I opened the file and I saw all the commands to insert every piece of data and reconstruct my schema. However when I try to restore that file it gets me the following error: C:\>pg_restore [filename] pg_restore: [archiver] input file does not appear to be a valid archive What I am doing wrong?? Any ideas?? Thank you in advanced. MAGO _________________________________________________________________ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Hi, Every time I have to specify a table name in a query I find myself prefixing the name of the table with the schema name as in schema.table. Is there a way to force the queries to look in one particular schema whenever I do not specify a schema name? thanks dilan
Nick, > To restore from a plain-text dump file, just pipe it into > psql like so: > > cat [filename] | psql [dbname] To nit-pick, this is a "useless use of cat". In UNIX-land, simple input redirection will work much better: psql [dbname and various options] < [filename] It's also less to type! :) I also believe this works in certain Windows operating systems, which is what it appears Mago is using. -------------------------- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Nick Fankhauser [mailto:nickf@ontko.com] > Sent: Tuesday, July 22, 2003 1:30 PM > To: Marvin; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] pg_restore problem!!! > > > Mago- > > pg_restore is used to restore a dump file created in one of > the non-text > formats such as tar format. > > To restore from a plain-text dump file, just pipe it into > psql like so: > > cat [filename] | psql [dbname] > > -Nick > > --------------------------------------------------------------------- > Nick Fankhauser > > nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marvin > Sent: Tuesday, July 22, 2003 3:22 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] pg_restore problem!!! > > > I made a back up file with this sentence: > > pg_dump -U [superusername] -C -D -f [filename] [dbname] > > It seems to work fine. I opened the file and I saw all the commands to > insert every piece of data and reconstruct my schema. However > when I try to > restore that file it gets me the following error: > > C:\>pg_restore [filename] > pg_restore: [archiver] input file does not appear to be a > valid archive > > What I am doing wrong?? > Any ideas?? > > Thank you in advanced. > > > MAGO > > > _________________________________________________________________ > IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Thank you. You saved my day!!! That command restores all my data into my DB. MAGO -------Mensaje original------- De: David Olbersen Fecha: Martes, 22 de Julio de 2003 03:02:48 p.m. Asunto: Re: [ADMIN] pg_restore problem!!! > To restore from a plain-text dump file, just pipe it into > psql like so: > > cat [filename] | psql [dbname] To nit-pick, this is a "useless use of cat". In UNIX-land, simple input redirection will work much better: psql [dbname and various options] < [filename] It's also less to type! :) I also believe this works in certain Windows operating systems, which is what it appears Mago is using. -------------------------- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Nick Fankhauser [mailto:nickf@ontko.com] > Sent: Tuesday, July 22, 2003 1:30 PM > To: Marvin; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] pg_restore problem!!! > > > Mago- > > pg_restore is used to restore a dump file created in one of > the non-text > formats such as tar format. > > To restore from a plain-text dump file, just pipe it into > psql like so: > > cat [filename] | psql [dbname] > > -Nick > > --------------------------------------------------------------------- > Nick Fankhauser > > nickf@doxpop.com Phone 1.765.965.7363 Fax 1.765.962.9788 > doxpop - Court records at your fingertips - http://www.doxpop.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Marvin > Sent: Tuesday, July 22, 2003 3:22 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] pg_restore problem!!! > > > I made a back up file with this sentence: > > pg_dump -U [superusername] -C -D -f [filename] [dbname] > > It seems to work fine. I opened the file and I saw all the commands to > insert every piece of data and reconstruct my schema. However > when I try to > restore that file it gets me the following error: > > C:\>pg_restore [filename] > pg_restore: [archiver] input file does not appear to be a > valid archive > > What I am doing wrong?? > Any ideas?? > > Thank you in advanced. > > > MAGO > > > _________________________________________________________________ > IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí > > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster . | |||
IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Вложения
On Tue, 22 Jul 2003, Dilan Arumainathan wrote: > Hi, > Every time I have to specify a table name in a query I find myself prefixing > the name of the table with the schema name as in schema.table. Is there a > way to force the queries to look in one particular schema whenever I do not > specify a schema name? Just change search_path to include it: psql > set search_path=$user,public,mynewschema;
> To nit-pick, this is a "useless use of cat". > > In UNIX-land, simple input redirection will work much better: > > psql [dbname and various options] < [filename] Good point... to elaborate further, the reason I was in a piping mindset is that with a large database, it also makes sense to compress on the fly to avoid filesystem size limits, so I usually use this pair of commands for backup/restore: pg_dump dbname | gzip > dbdumpfile.gz gunzip -c dbdumpfile | psql dbname -Nick
On Tuesday 22 July 2003 22:52, Dilan Arumainathan wrote: > Hi, > Every time I have to specify a table name in a query I find myself > prefixing the name of the table with the schema name as in schema.table. Is > there a way to force the queries to look in one particular schema whenever > I do not specify a schema name? SET search_path TO ... http://www.postgresql.org/docs/7.3/static/ddl-schemas.html You can also use ALTER USER user_name SET search_path to ...; to make the setting permanent. Ian Barwick barwick@gmx.net
Nick, > pg_dump dbname | gzip > dbdumpfile.gz > > gunzip -c dbdumpfile | psql dbname If you're compressing why not use: pg_dump -Fc dbname -f dbdumpfile pg_restore dbdumpfile ? Don't take this the wrong way, I'm genuinely curious! When I first started I didn't realize we did our dumps this way andtried to gzip one of the dump files: and got 0% savings! If this is a way to reduce the size of my nightly dumps I'm all for it! :) -------------------------- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Nick Fankhauser [mailto:nickf@ontko.com] > Sent: Tuesday, July 22, 2003 2:05 PM > To: David Olbersen; pgsql-admin@postgresql.org > Subject: RE: [ADMIN] pg_restore problem!!! > > > > > To nit-pick, this is a "useless use of cat". > > > > In UNIX-land, simple input redirection will work much better: > > > > psql [dbname and various options] < [filename] > > Good point... to elaborate further, the reason I was in a > piping mindset is > that with a large database, it also makes sense to compress > on the fly to > avoid filesystem size limits, so I usually use this pair of > commands for > backup/restore: > > pg_dump dbname | gzip > dbdumpfile.gz > > gunzip -c dbdumpfile | psql dbname > > -Nick > >
Hi David- I don't use the -Fc or -Ft format exclusively because I ran into dependency problems with the order of object creation when trying to do a full database restore using pg_restore and these formats. Since we have a fair amount of space, I do both a tar format and a plain-text format dump every night. This gives me the option of quickly restoring the entire database from the plain text version (which has no dependency problems), but then also easily restoring a single object or group of objects using pg_restore from the tar format. I haven't explored using the c (custom/compressed) format because when I inquired about the dependency problems I encountered with the tar format, I learned that this was a known problem in every format except plain text. If you're using only -Fc format, you might want to try running a full restore to make sure you don't have to reorder elements during recovery. I opted to do both formats because our database is subject to regular structure changes, so I can't tell how elements will have to be re-ordered in advance. I like all of my restore pre-plans to be simple enough to execute quickly and with little thought while many people are talking to me. (Because it is well-known among most users that distracting your SysAdmin with frequent visits and phone calls makes him work faster and ensures that your system gets restored more quickly.) -NF > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of David Olbersen > Sent: Tuesday, July 22, 2003 4:15 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] pg_restore problem!!! > > > Nick, > > > pg_dump dbname | gzip > dbdumpfile.gz > > > > gunzip -c dbdumpfile | psql dbname > > If you're compressing why not use: > > pg_dump -Fc dbname -f dbdumpfile > pg_restore dbdumpfile > > ? > > Don't take this the wrong way, I'm genuinely curious! When I > first started I didn't realize we did our dumps this way and > tried to gzip one of the dump files: and got 0% savings! > > If this is a way to reduce the size of my nightly dumps I'm all for it! :) > > -------------------------- > David Olbersen > iGuard Engineer > St. Bernard Software > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > > -----Original Message----- > > From: Nick Fankhauser [mailto:nickf@ontko.com] > > Sent: Tuesday, July 22, 2003 2:05 PM > > To: David Olbersen; pgsql-admin@postgresql.org > > Subject: RE: [ADMIN] pg_restore problem!!! > > > > > > > > > To nit-pick, this is a "useless use of cat". > > > > > > In UNIX-land, simple input redirection will work much better: > > > > > > psql [dbname and various options] < [filename] > > > > Good point... to elaborate further, the reason I was in a > > piping mindset is > > that with a large database, it also makes sense to compress > > on the fly to > > avoid filesystem size limits, so I usually use this pair of > > commands for > > backup/restore: > > > > pg_dump dbname | gzip > dbdumpfile.gz > > > > gunzip -c dbdumpfile | psql dbname > > > > -Nick > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >