Re: How to restore backup

On Fri, Feb 24, 2012 at 1:51 AM, Steven Marshall <..hidden..> wrote:

On Thu, Feb 23, 2012 at 5:36 AM, Chris Travers <..hidden..> wrote:

On Wed, Feb 22, 2012 at 7:36 PM, Steven Marshall <..hidden..> wrote:

On Thu, Feb 23, 2012 at 1:20 AM, Chris Travers <..hidden..> wrote:

On Wed, Feb 22, 2012 at 7:17 PM, Steven Marshall <..hidden..> wrote:

On Wed, Feb 22, 2012 at 11:04 PM, Chris Travers <..hidden..> wrote:

On Wed, Feb 22, 2012 at 9:11 AM, Steven Marshall <..hidden..> wrote:

I am in the process of moving Ledgersmb 1.3.11 to another server. On my old server using admin.pl I have backed up my database as well as roles. How should I restore both database and roles on my new server? Using pg_restore?

Use psql to restore the roles and pg_restore to restore the database.

Typically this will be something like:

psql -U postgres -f my_roles_file
pg_restore -U postgres -C my_backup_file

Note that this assumes that your database does not exist on the new server. You should drop it before.

The roles backup only backs up some cluster-wide globals, like roles. The database it is restored to doesn't matter.

The second creates the database named in the backup, and restores the backup to the database it just created. This is of course not the only thing you can do with your backup. See the pg_restore man page for many more details.

Best Wishes,
Chris Travers

I restored the roles OK, but getting an error when trying to restore my database. I tried two different commands but get the same offset error. See below.

..hidden..:~> pg_restore -C backup_StallusBoutique_Test_2012-02-22.bak
pg_restore: [archiver] unexpected data offset flag 0

..hidden..:~> pg_restore -C -d postgres backup_StallusBoutique_Test_2012-02-22.bak
pg_restore: [archiver] unexpected data offset flag 0

Are the encodings different in the databases? Different PostgreSQL versions? Anything else different?

Best Wishes,
Chris Travers

Not sure about the encodings or how to check. As for the versions, I made my backup from a PostgreSQL server version 9.1.1-3.1.4 and trying to restore to server version 9.0.3-1.6.1

You could try using the pg_restore on the 9.1 system along with -h pointing to the 9.0 server.

Best Wishes,
Chris Travers

It was easier to start over and recreate my test database on my 9.0.3 PostgreSQL server. After doing so, I went back to setup.pl and generated a backup of my StallusBoutique_Test database. I want to take this backup and restore into another database called StallusBoutique so I end up with two databases identical. Using setup.pl I created a new database called StallusBoutique. At that point, I issued the following command in a terminal for the purpose of restoring my backup into this new database but I get a Segmentation fault. Not exactly sure what this is or how to work around this.

..hidden..:~> pg_restore -d StallusBoutique backup_StallusBoutique_Test_2012-02-24.bak

Segmentation fault
..hidden..:~>

The point of this exercise is to define a process where I can recreate both a production and test environments so that in the future as we upgrade I can test the upgrade prior to upgrading a production server. Most likely, I would be taking a backup of a production environment and restoring it to a test environment.

--
Best Regards,
Steven Marshall