Thread: pg_dump, pg_restore and template relationships

I'm moving a PostgreSQL cluster from one server to another, and in the
process also moving from 7.4 to 8.1. On the home server I have a
template database called template_postgis that I used to create a
couple other databases.  According to the PostgreSQL docs, changes made
to a template will be effected on databases created from that
template.  I'm curious how, if at all, this relationship is maintained
through a pg_dump and pg_restore?  In particular, let's say I have
template "A" and I create database "B" from it.  If I want to dump "B"
and restore it elsewhere, will the dump of "B" include all of the
functions / types that it inherits from "A", or for to maintain
functionality do I need to dump and restore "A" first, then "B"
afterwards?

I've looked through the docs on pg_dump and pg_restore but I wasn't
able to find the answers to this.

Thank you,
Mark

Mark S <mark@gravitycollege.com> writes:
> According to the PostgreSQL docs, changes made
> to a template will be effected on databases created from that
> template. I'm curious how, if at all, this relationship is maintained
> through a pg_dump and pg_restore?

It isn't.  CREATE DATABASE merely copies the source database as it stands
at that instant, there's no persistent relationship.

If you like you can create the target database by hand, using the new
template database, before restoring into it; but what is likely to
happen is that you get a lot of 'duplicate object' errors from objects
that were in the dump due to having been copied from the previous
incarnation of the template.  Sometimes this is okay, but you'll want to
scan through the messages and make sure nothing undesirable happened.

            regards, tom lane