Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.klaver@xxxxxxxxx

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux