Re: Server/Data Migration Advice

On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> The reason I pointed to the manual links is that there is a lot of good
> information in there. It deserves more than a skim:). Realistically, to get the
> most out of the dump/restore process you need to know the options available on
> both sides of the procedure.  For instance the -C option to pg_dump, puts in
> command to create database on restore, saves the step of creating a database on
> the other end.  The issues that may arise are most likely going to be generic to
> the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the
> Release Notes for the  covered versions, in particular the Migration section. In
> this case the notes for 9.0 that cover the migration issues from 8.4 and the
> notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it
> depends on what you have done in your database. What form of pg_dump you use is
> up to you.  I will say, the custom format, -Fc, has some interesting features.
> One, it is compressed. Two, you can restore from it in full or pick and
> choose(within reason,see docs) those items you wish to restore without resorting
> to cut and paste.

On Thu, Dec 15, 2011 at 10:28 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> The reason I pointed to the manual links is that there is a lot of good
> information in there. It deserves more than a skim:). Realistically, to get the
> most out of the dump/restore process you need to know the options available on
> both sides of the procedure. For instance the -C option to pg_dump, puts in
> command to create database on restore, saves the step of creating a database on
> the other end. The issues that may arise are most likely going to be generic to
> the upgrade from 8.4 to 9.1. To get a handle on those it is best to read the
> Release Notes for the covered versions, in particular the Migration section. In
> this case the notes for 9.0 that cover the migration issues from 8.4 and the
> notes for 9.1 that cover same from 9.0. Not all the issues may affect you, it
> depends on what you have done in your database. What form of pg_dump you use is
> up to you. I will say, the custom format, -Fc, has some interesting features.
> One, it is compressed. Two, you can restore from it in full or pick and
> choose(within reason,see docs) those items you wish to restore without resorting
> to cut and paste.

So after reading
http://www.postgresql.org/docs/9.1/interactive/backup-dump.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.

"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.