Re: Server/Data Migration Advice

On Thursday, December 15, 2011 7:57:40 am Carlos Mennens wrote:

> So after reading
> http://www.postgresql.org/docs/9.1/interactive/backup-dump.html,

That was not the link I posted. In fact I have never actually been to that
page:) This is the link I posted:
http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html

>
> I'm not sure why the manual shows you in "24.1. SQL Dump" & then
> directly after in 24.1.1, they explain how to restore with psql as you
> advised me not to. I got my psql db_name < infile command directly
> from the manual. I know it's personal preference but from everything
> you noted, why didn't they just explain how to perform a pg_restore in
> the "24.1.1. Restoring the Dump" section.

First I did not advise against using a plain text dump, just noted there are
options. You have stumbled across the reason I mentioned the options. One of
the quirks of Postgres is that there is one dump command(pg_dump), but two ways
to restore (psql, pg_restore), depending on the format of the dump. What is
being shown in the section you refer to is the plain text(SQL) method. To
restore a plain text dump you need to use psql. You can do it as shown or by
using psql -f 'dump.sql' The reason to use -f is found here:

http://www.postgresql.org/docs/9.1/interactive/app-psql.html
"
-f filename
--file=filename

Use the file filename as the source of commands instead of reading commands
interactively. After the file is processed, psql terminates. This is in many ways
equivalent to the internal command \i.

If filename is - (hyphen), then standard input is read.

Using this option is subtly different from writing psql < filename. In
general, both will do what you expect, but using -f enables some nice features
such as error messages with line numbers. There is also a slight chance that
using this option will reduce the start-up overhead. On the other hand, the
variant using the shell's input redirection is (in theory) guaranteed to yield
exactly the same output you would have received had you entered everything by
hand.
"

If you do one of the non-text dumps then you will need to use pg_restore:
http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html

>
> "24.1.3. Handling Large Databases" section is very cool but also
> extremely vague IMO.
>
> > Use pg_dump's custom dump format. If PostgreSQL was built on a system
> > with the zlib compression library installed, the custom dump format will
> > compress data as it writes it to the output file. This will produce dump
> > file sizes similar to using gzip, but it has the added advantage that
> > tables can be restored selectively. The following command dumps a
>
> > database using the custom dump format:
> So this seems helpful to myself in only that A: the dump is compressed
> (my databases are generally small anyways) and B: I don't have to
> create the database before I restore it. My only question is I see
> that noted nowhere in the manual ... yet but I'm just wondering if
> that's a correct statement.

It is noted if you go to the pg_dump link shown above:)

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com