Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    33

    Unanswered: Is there a way to dump only a selected schema?

    We have a huge DB with a lot of schema, but a lot of them aren't changing anyway. So, when making a dump, there is a lot of wasted space with all of this schemas.

    I'm looking for a command that would permit us to save a schema and all his data without knowing all the table names and structure.

  2. #2
    Join Date
    Aug 2003
    Posts
    32
    This may help:

    psql -d template1
    create database tmp;
    \c tmp
    create table one (two int);
    create table three (four varchar(50));
    \q
    pg_dump -t one tmp
    pg_dump -t three tmp

    The -t switch will only dump the named table.

  3. #3
    Join Date
    Oct 2003
    Posts
    33
    That's exactly what i DON'T want. I'm not able to know all the table, because they may change often and the backup script don't have to be change each time a table is renamed or added or removed.

  4. #4
    Join Date
    Aug 2003
    Posts
    32
    OK, how about this:

    <?php

    if (!pg_connect("host=my_host dbname=my_database user=my_user password=my_password")) {
    print "failed to connect.<br>";
    exit;
    }
    if (!$result = pg_query("select * from pg_tables where schemaname = 'public'")) {
    print "failed to select table names.<br>";
    exit;
    }
    while ($row = pg_fetch_array($result)) {
    print "pg_dump -t {$row["tablename"]} my_database<br>";
    }
    ?>

    Could that PHP script help? Maybe if did something like that, but wrote the output to a file (rather than printing it) and then executed the file you'd get what you want?

    It seems that when you're connected to a particular database, the table names can be found by selecting tablename from pg_tables where schemaname = 'public'. Well that's how it appears from my experiment. I can't be sure if this will always be the case.
    Last edited by stacey_richards; 01-21-04 at 06:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •