Thread: pg_restore vs psql

Hi,

can one tell me whether there are main differences in using
pg_restore of "psql -f mydb.dump mydb" on a file created with pg_dump.

Thaks Erwin

* Erwin Ambrosch <naus.office@aon.at> [26.04.2003 15:53]:
> Hi,
>
> can one tell me whether there are main differences in using
> pg_restore of "psql -f mydb.dump mydb" on a file created with pg_dump.

psql understands only plain SQL commands. pg_dump can create custom format
dumps, gzipped dumps and tarballs along with SQL dumps.

You cannot feed, say, custom dump to psql.

--

Victor Yegorov

Erwin-

If you created your dump file using the -Fc or -Ft format switches, you'll
need to use pg_restore to restore from the dump file. Without the format
switches, pg_dump creates a plain text file of psql commands.

Generally, I'd say that if you intend to restore the entire contents of your
dump file, then using the plain text format (& compressing with gzip if
needed) is the way to go.

If you need the ability to selectively restore some part of the dump file,
(like one table) then you should use one of the non-text formats for pg_dump
and thus need to restore using pg_restore.

I've learned that doing a complete restore of a non-text dump file using
pg_restore is a real pain, so our backup strategy includes creating both a
tar-format and plain text dump file of the entire database so we are
positioned to do either a partial or full restore with ease.

Regards,
     -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 Erwin Ambrosch
> Sent: Saturday, April 26, 2003 7:48 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] pg_restore vs psql
>
>
> Hi,
>
> can one tell me whether there are main differences in using
> pg_restore of "psql -f mydb.dump mydb" on a file created with pg_dump.
>
> Thaks Erwin
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

also, if you store blobs in your database then you need to use pg_dump and
pg_restore to backup/restore them.
----- Original Message -----
From: "Nick Fankhauser" <nickf@ontko.com>

> Generally, I'd say that if you intend to restore the entire contents of
your
> dump file, then using the plain text format (& compressing with gzip if
> needed) is the way to go.
>
> If you need the ability to selectively restore some part of the dump file,
> (like one table) then you should use one of the non-text formats for
pg_dump
> and thus need to restore using pg_restore.