Re: How to restore from backup to 8.4.3 server using 9.0 dump/restore

On Thursday 16 December 2010 12:16:47 am Andrus Moor wrote:
> > Another testimonial to the stability of Postgres :)
> > We may be arguing semantics
> > here but I would consider dump/restore an admin function. How do you
> > handle a
> > client restoring a database currently?
>
> Database is 8.0 compliant.
> In this case 8.4 pg_dump/pg_restore is used to dump and restore with any
> same version 8 of server witthout issues.

First from here:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
"Also, it is not guaranteed that pg_dump's output can be loaded into a server of
an older major version — not even if the dump was taken from a server of that
version. Loading a dump file into an older server may require manual editing of
the dump file to remove syntax not understood by the older server. "

The fact that it worked without intervention before is as much luck as anything
else. Depending on that behavior going forward is not prudent.

>
> This was broken in 9:
> Postgres 9 emits invalid "create procedural language plpgsql" command which
> does not work in any other version.
> How to fix this without distributing two copies of pg_dump/pg_restore ?
> Is it reasonable to create database and plpgsql language manually before
> running pg_restore ? In this case invalid "create procedural language
> plpgsql" issued by pg_restore gets ignored and maybe restore succeeds?

This is an example of the above. Two things at work here. First in 9.0 the
plpgsql language is loaded automatically. Two it is loaded using the CREATE OR
REPLACE LANGUAGE form of SQL, which did not exist prior to 9.0. It is the
REPLACE clause that is causing the problem. You could try creating the language
ahead of time and see if it just skips over the error. Another option would be
to use the -l switch to pg_restore to generate a list of the TOC entries for
the dump and store in a file. You then comment out the one for plpgsql and then
use the -L switch to read the edited file. See here for more detail:

http://www.postgresql.org/docs/9.0/interactive/app-pgrestore.html

>
> > I could see a client connecting to one
> > of the system dbs and doing a DROP DATABASE.
> > From your earlier messages the
> > implication was that you used pg_restore to repopulate the db. My
> > question then
> > is how do the clients make sure that they are not doing this on an active
> > database
> > and keep it from going active during the process?
>
> Applicaton asks for new database name and verifies that this does not exist
> before executing
> pg_restore.
>
> Another requirement is to clone existing database in server with data. I
> posted question about it and it seems that PostgreSql does not have any
> capability to do this in server side in plpgsql fast.
> So we need to use slow and unsafe dump/restore over internet for this also.

Yea the CREATE DATABASE TEMPLATE option is as close as it gets and you are
already aware of its limitations.

>
> Andrus.

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