Thread: Backups and restores.
I am upgrading my workstation to 7.3.3. I have some database backups
that were created with this command.
/usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename
Which generates this sort of file.
pgdump_2003-6-5-csp.gz
Formatted like so.
\connect - postgres
CREATE SEQUENCE "time_periods_id_seq" start 3 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
SELECT nextval ('"time_periods_id_seq"');
CREATE SEQUENCE "length_of_time_id_seq" start 5 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
SELECT nextval ('"length_of_time_id_seq"');
CREATE SEQUENCE "depts_id_seq" start 61 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
SELECT nextval ('"depts_id_seq"');
CREATE SEQUENCE "divisions_id_seq" start 4 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;
And so on and so forth.
When I try to use pg_restore from 7.3.3 to restore that file with this
command.
./bin/pg_restore -d csp pgdump_2003-6-5-csp
Or even this one.
./bin/pg_restore -d csp pgdump_2003-6-5-csp.gz
I get this error.
pg_restore: [archiver] input file does not appear to be a valid archive
So what sort of terribly obvious thing am I doing wrong?
I tried to do a search for this in the mailing list archives but the
database is apparently temporarily down. :) Go figure. Just when I
need it.
Thanks for the help all.
--
Brian Avis
SEARHC Medical Clinic
Juneau, AK 99801
(907) 463-4049
Have a nice diurnal anomaly!
On 6/6/03 7:09 PM, "Brian Avis" <brian.avis@searhc.org> wrote:
>
> I am upgrading my workstation to 7.3.3. I have some database backups
> that were created with this command.
>
> /usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename
>
> Which generates this sort of file.
>
> pgdump_2003-6-5-csp.gz
>
> Formatted like so.
>
> \connect - postgres
> CREATE SEQUENCE "time_periods_id_seq" start 3 increment 1 maxvalue
> 2147483647 minvalue 1 cache 1 ;
> SELECT nextval ('"time_periods_id_seq"');
> CREATE SEQUENCE "length_of_time_id_seq" start 5 increment 1 maxvalue
> 2147483647 minvalue 1 cache 1 ;
> SELECT nextval ('"length_of_time_id_seq"');
> CREATE SEQUENCE "depts_id_seq" start 61 increment 1 maxvalue 2147483647
> minvalue 1 cache 1 ;
> SELECT nextval ('"depts_id_seq"');
> CREATE SEQUENCE "divisions_id_seq" start 4 increment 1 maxvalue
> 2147483647 minvalue 1 cache 1 ;
>
>
> And so on and so forth.
>
> When I try to use pg_restore from 7.3.3 to restore that file with this
> command.
> ./bin/pg_restore -d csp pgdump_2003-6-5-csp
> Or even this one.
> ./bin/pg_restore -d csp pgdump_2003-6-5-csp.gz
>
> I get this error.
>
> pg_restore: [archiver] input file does not appear to be a valid archive
>
> So what sort of terribly obvious thing am I doing wrong?
>
> I tried to do a search for this in the mailing list archives but the
> database is apparently temporarily down. :) Go figure. Just when I
> need it.
>
> Thanks for the help all.
>
>
Hi Brian:
I'm not sure that pg_restore works with the default output from pg_dump.
Pg_dump has several additional formats, and these I believe can be used as
inputs to pg_dump.
Regardless of whether I'm correct on that point, you should be able to load
these files from within psql by using the \i command. If the files don't
contain the necessary database creation commands, you may need to create the
databases first, then connect to them, before executing the file.
-- sgl
=======================================================
Steve Lane
Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607
Voice: (312) 433-2421 Email: slane@moyergroup.com
Fax: (312) 850-3930 Web: http://www.moyergroup.com
=======================================================
Brian Avis writes: > I am upgrading my workstation to 7.3.3. I have some database backups > that were created with this command. > > /usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename > pg_restore: [archiver] input file does not appear to be a valid archive These archives are supposed to be restored using psql. The pg_dump reference page should tell you the details. -- Peter Eisentraut peter_e@gmx.net
Brian Avis <brian.avis@searhc.org> writes:
> /usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename
> ./bin/pg_restore -d csp pgdump_2003-6-5-csp.gz
> pg_restore: [archiver] input file does not appear to be a valid archive
> So what sort of terribly obvious thing am I doing wrong?
You want something like
gzcat pgdump_2003-6-5-csp.gz | psql csp
pg_restore is for working with -Fc or -Ft output from pg_dump, not the
plain-text script output.
regards, tom lane
Brian Avis <brian.avis@searhc.org> writes: > I am upgrading my workstation to 7.3.3. I have some database backups > that were created with this command. > > /usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename > > Which generates this sort of file. > > pgdump_2003-6-5-csp.gz For dumps in SQL format (the default), feed them to 'psql' rather than using 'pg_restore', which is for "tar" and "custom" format dumps. -Doug
On Saturday 07 Jun 2003 1:09 am, Brian Avis wrote: > I am upgrading my workstation to 7.3.3. I have some database backups > that were created with this command. > > /usr/local/pgsql/bin/pg_dump $db | gzip > > /usr/local/pgsql/backups/$filename [snip] > \connect - postgres > CREATE SEQUENCE "time_periods_id_seq" start 3 increment 1 maxvalue > 2147483647 minvalue 1 cache 1 ; [snip] > I get this error. > > pg_restore: [archiver] input file does not appear to be a valid archive > > So what sort of terribly obvious thing am I doing wrong? To quote from 'man pg_restore' "pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump(1) in one of the non-plain-text formats." You've got plain-text there. You can restore it with psql -f ... > I tried to do a search for this in the mailing list archives but the > database is apparently temporarily down. :) Go figure. Just when I > need it. Disk failure on one of the machines, I believe. -- Richard Huxton
> /usr/local/pgsql/bin/pg_dump $db | gzip > > /usr/local/pgsql/backups/$filename Try gzip -d $filename before restoring -- you probably have to unzip the file. (The "gzip" is what compressed it.) Jeff
Have you tried: zcat /usr/local/pgsql/backups/pgdump_2003-6-5-csp.gz | psql -d csp and looked in http://www.us.postgresql.org/postgresql-7.3.3/backup.html#BACKUP-DUMP-RESTOR E Cheers, Murthy >-----Original Message----- >From: Brian Avis [mailto:brian.avis@searhc.org] >Sent: Friday, June 06, 2003 20:09 >To: pgsql-general@postgresql.org >Subject: [GENERAL] Backups and restores. > > > >I am upgrading my workstation to 7.3.3. I have some database backups >that were created with this command. > >/usr/local/pgsql/bin/pg_dump $db | gzip > >/usr/local/pgsql/backups/$filename > >Which generates this sort of file. > >pgdump_2003-6-5-csp.gz > >Formatted like so. > >\connect - postgres >CREATE SEQUENCE "time_periods_id_seq" start 3 increment 1 maxvalue >2147483647 minvalue 1 cache 1 ; >SELECT nextval ('"time_periods_id_seq"'); >CREATE SEQUENCE "length_of_time_id_seq" start 5 increment 1 maxvalue >2147483647 minvalue 1 cache 1 ; >SELECT nextval ('"length_of_time_id_seq"'); >CREATE SEQUENCE "depts_id_seq" start 61 increment 1 maxvalue >2147483647 >minvalue 1 cache 1 ; >SELECT nextval ('"depts_id_seq"'); >CREATE SEQUENCE "divisions_id_seq" start 4 increment 1 maxvalue >2147483647 minvalue 1 cache 1 ; > > >And so on and so forth. > >When I try to use pg_restore from 7.3.3 to restore that file with this >command. >./bin/pg_restore -d csp pgdump_2003-6-5-csp >Or even this one. >./bin/pg_restore -d csp pgdump_2003-6-5-csp.gz > >I get this error. > >pg_restore: [archiver] input file does not appear to be a valid archive > >So what sort of terribly obvious thing am I doing wrong? > >I tried to do a search for this in the mailing list archives but the >database is apparently temporarily down. :) Go figure. Just when I >need it. > >Thanks for the help all. > > > >-- >Brian Avis >SEARHC Medical Clinic >Juneau, AK 99801 >(907) 463-4049 >Have a nice diurnal anomaly! > > > >---------------------------(end of >broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly >
> Brian Avis writes:
>
>> I am upgrading my workstation to 7.3.3. I have some database
>> backups that were created with this command.
>>
>> /usr/local/pgsql/bin/pg_dump $db | gzip >
>> /usr/local/pgsql/backups/$filename
>
>> pg_restore: [archiver] input file does not appear to be a valid
>> archive
>
> These archives are supposed to be restored using psql. The pg_dump
> reference page should tell you the details.
>
Gee, the man page of pg_restore says:
DESCRIPTION
pg_restore is a utility for restoring a PostgreSQL
database from an archive created by pg_dump(1) in one of
the NON-PLAIN-TEXT (emphasis added) formats.
;)
~Berend Tober
On Fri, 6 Jun 2003, Brian Avis wrote: > > I am upgrading my workstation to 7.3.3. I have some database backups > that were created with this command. > > /usr/local/pgsql/bin/pg_dump $db | gzip > /usr/local/pgsql/backups/$filename > > Which generates this sort of file. > > pgdump_2003-6-5-csp.gz > When I try to use pg_restore from 7.3.3 to restore that file with this > command. > ./bin/pg_restore -d csp pgdump_2003-6-5-csp > Or even this one. > ./bin/pg_restore -d csp pgdump_2003-6-5-csp.gz > > I get this error. > > pg_restore: [archiver] input file does not appear to be a valid archive Are you un gzipping the file before feeding it to pg_restore?