Re: Server/Data Migration Advice

On Thursday, December 15, 2011 6:55:46 am Carlos Mennens wrote:
> On Thu, Dec 15, 2011 at 9:37 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
wrote:
> > You know the fine manual covers this?:
> > http://www.postgresql.org/docs/9.1/interactive/app-pgdump.html
>
> I've honestly skimmed the manual and it's very easy to use and good
> but it covers so many aspects and sometimes I need a specific answer
> for my situation.
>
> > In the case above you seemed to have used the plain text format, in the
> > future should you use a non text format the restore process is here:
> > http://www.postgresql.org/docs/9.1/interactive/app-pgrestore.html
>
> Are you saying I dumped the database in a text formal or I'm restoring
> the database infile in plain text? I'll read that 2nd URL link you
> posted. What are the issues with using pg_dump to dump and then 'psql
> some_db < pg_dump.sql'? I show the manual explains how to perform this
> action and what the system is doing but doesn't explain why this is an
> issue or why you recommended to use pg_restore instead?

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.

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