How to replicate a schema?

Bob Nix
  • Bob Nix

    Bob Nix - 2004-02-03

    I wish to replicate an entire schema - all tables, keys, indexes, sequences, views, functions, and data.  I will need to to do this eventually through a script, so that I can reset a schema, on demand,  to a starting point.  (We are using this schema for a product demonstration, and need the ability to run multiple scemas for multiuple users, as well as reset each schema on demand for the next demonstration.)  What's the best approach?  We will have a base "master" schema, rarely changed, as a source for the loads.  It would be useful if the load re-created all the components so that changes to the master would be reflected in the copies. 


    • Bob Nix

      Bob Nix - 2004-02-03

      Additional information - my database has blobs, so simple sql will not suffice.  I am using
      pg_dump -b -Fc demo > xx.dump
      as the command line to dump the database.  I cannot restore this database to a new database
      =# drop database abcabc;
      =# create database abcabc;
      pg_restore -d abcabc xx.dump

      ...many lines...
      pg_restore: NOTICE:  CREATE TABLE will create implicit sequence 'fin_transaction_transaction_id_seq' for SERIAL column 'fin_transaction.transaction_id'
      ...many lines...
      pg_restore: [archiver (db)] could not execute query: ERROR:  Relation 'fin_transaction_transaction_id_seq' already exists

      So pg_restore creates an implicit sequence when the table is created, and then errors out when the actual sequence should be created - yet it created many of the sequences that are defined and used identically.  The restore cerated 113 of 124 tables, 110 of 131 views, 4 of 6 functions, 76 of 89 sequences, but has not yet gotten to the index creation.

      • Christopher Kings-Lynne

        This isn't anything to do with phpPgAdmin, I suggest you ask on one of the postgresql support lists.



Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks