Обсуждение: problem backup/restore PSQL DB

It looks like it's a user configuration issue.  I have hit user setup issues
while restoring DB's before and generally I find it useful to create the
Users and/or Groups manually on the new server first before restoring the
DB's whenever I hit problems like that.  Try syncing the users manually,
(assuming that there are only a few and that it could be done manually with
some level of ease) and then try restoring again.

-Paul
--
View this message in context: http://www.nabble.com/problem-backup-restore-PSQL-DB-tf1995768.html#a5478199
Sent from the PostgreSQL - admin forum at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

You can also try using 'pg_dumpall' ( http://www.postgresql.org/docs/8.1/static/app-pg-dumpall.html) that will take the dump of all the users, groups and databases.

Thanks,

Shoaib Mir
EnterpriseDB

On 7/25/06, Paul S <plabrh1@gmail.com> wrote:

It looks like it's a user configuration issue.  I have hit user setup issues
while restoring DB's before and generally I find it useful to create the
Users and/or Groups manually on the new server first before restoring the
DB's whenever I hit problems like that.  Try syncing the users manually,
(assuming that there are only a few and that it could be done manually with
some level of ease) and then try restoring again.

-Paul
--
View this message in context: http://www.nabble.com/problem-backup-restore-PSQL-DB-tf1995768.html#a5478199
Sent from the PostgreSQL - admin forum at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

Thanks for helping. My postgresql DB is actually part of the OME system which stores Image Metadata. The system overview is as following: http://www.openmicroscopy.org/system-overview/.

So I am actually using the option provided by OME to backup/restore my system.
http://www.openmicroscopy.org/system-admin/backup_restore.html

Part of the backup code in perl is as following:

    print "    \\_ Backing up postgress database ome\n";
    my $dbConf = $environment->DB_conf();
    my $dbName = 'ome';
    $dbName = $dbConf->{Name} if $dbConf->{Name};

    my $flags = '';
    $flags .= '-h '.$dbConf->{Host}.' ' if $dbConf->{Host};
    $flags .= '-p '.$dbConf->{Port}.' ' if $dbConf->{Port};
    $flags .= '-U '.$dbConf->{User}.' ' if $dbConf->{User};
    $flags .= '-Fc'; # -F (format).
                     # -p: use the plain text SQL script file this should be the most portable
                     # -c: custom archive suitable for input into pg_restore
    print STDERR "su $postgress_user -c '".$prog_path{'pg_dump'}." $flags -o $dbName > /tmp/omeDB_backup'\n";

    # backup database and watch output from pg_dump
    foreach (`su $postgress_user -c '$prog_path{'pg_dump'} $flags -o $dbName > /tmp/omeDB_backup' 2>&1`) {
        print STDERR "\nDatabase Backup Failed: $_" and die if $_ =~ /pg_dump/ or $_ =~ /ERROR/ or $_ =~ /FATAL/;
    } 
    # check the size of omeDB_backup
    if (stat("/tmp/omeDB_backup")->size < 1024) {
        print STDERR "\nDatabase Backup Failed: /tmp/omeDB_backup is less than 1024 bytes in size \n";
        die;
    }
    # log version of backup
    open (FILEOUT, "> /tmp/OMEmaint") or die "Couldn't open OMEmaint for writing\n";
    print FILEOUT "version=$dbAdmin_version\n";
    close (FILEOUT);

I will try modify the 'pg_dump' to 'pg_dumpall' and see if it works..
But is it even possible to backup one system and restore it in another?  I just don't want to go into a deadend. Many thanks!!

Jun

On 7/25/06, Shoaib Mir < shoaibmir@gmail.com> wrote:

You can also try using 'pg_dumpall' ( http://www.postgresql.org/docs/8.1/static/app-pg-dumpall.html) that will take the dump of all the users, groups and databases.

Thanks,

Shoaib Mir
EnterpriseDB

On 7/25/06, Paul S <plabrh1@gmail.com> wrote:

It looks like it's a user configuration issue.  I have hit user setup issues
while restoring DB's before and generally I find it useful to create the
Users and/or Groups manually on the new server first before restoring the
DB's whenever I hit problems like that.  Try syncing the users manually,
(assuming that there are only a few and that it could be done manually with
some level of ease) and then try restoring again.

-Paul
--
View this message in context: http://www.nabble.com/problem-backup-restore-PSQL-DB-tf1995768.html#a5478199
Sent from the PostgreSQL - admin forum at Nabble.com.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly