pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedmove dbs from 8.1 to 8.4
6+ messages / 3 participants
[nested] [flat]
* move dbs from 8.1 to 8.4
@ 2013-10-10 20:17 Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 3 replies; 6+ messages in thread
From: Marc Fromm @ 2013-10-10 20:17 UTC (permalink / raw)
To: pgsql-admin
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the databases from the old server running fedora and postgresql 8.1 using this script.
#!/bin/bash
# Backup all Postgresql databases
# Location of the backup logfile.
logfile="/var/lib/pgsql/backups/logs/pg_back.log"
# Location to place backups.
backup_dir="/var/lib/pgsql/backups"
touch $logfile
timeslot=`date +%m-%d-%Y_%H-%M`
timeinfo=`date '+%T-%F'`
/usr/bin/vacuumdb -azh 127.0.0.1 -U postgres
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-all-$timeslot-databases.gz"
echo "Backup of all databses complete at $timeinfo for time slot $timeslot--pg_dumpall." >> $logfile
The new server is running postgresql 8.4.
When I restore the databases, with the commands below, only the databases are created, no tables and no data is restored.
* gunzip postgresql-all-10-10-2013_04-07-databases.gz
* psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres
The above works between two different servers that both run 8.1.
What am I missing to restore all my databases with tables and data from 8.1 to the new 8.4?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: move dbs from 8.1 to 8.4
@ 2013-10-10 20:52 Payal Singh <payal@omniti.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 0 replies; 6+ messages in thread
From: Payal Singh @ 2013-10-10 20:52 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
Can you use '-v' with pg_dumpall and output to the log file? That might
help. Likely something is not right with the individual pg_dump processes
that work inside the pg_dumpall.
Payal Singh,
OmniTi Computer Consulting Inc.
Junior Database Architect,
Phone: 240.646.0770 x 253
On Thu, Oct 10, 2013 at 4:17 PM, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
> I built a new server running centos 6.4 and postgresql 8.4. I backed up
> all the databases from the old server running fedora and postgresql 8.1
> using this script.****
>
> *#!/bin/bash*
>
> *# Backup all Postgresql databases*
>
> * *
>
> *# Location of the backup logfile.*
>
> *logfile="/var/lib/pgsql/backups/logs/pg_back.log"*
>
> * *
>
> *# Location to place backups.*
>
> *backup_dir="/var/lib/pgsql/backups"*
>
> *touch $logfile*
>
> *timeslot=`date +%m-%d-%Y_%H-%M`*
>
> *timeinfo=`date '+%T-%F'`*
>
> * *
>
> */usr/bin/vacuumdb -azh 127.0.0.1 -U postgres*
>
> */usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip >
> "$backup_dir/postgresql-all-$timeslot-databases.gz"*
>
> *echo "Backup of all databses complete at $timeinfo for time slot
> $timeslot--pg_dumpall." >> $logfile*
>
> ** **
>
> The new server is running postgresql 8.4. ****
>
> When I restore the databases, with the commands below, only the databases
> are created, no tables and no data is restored.****
>
> - *gunzip postgresql-all-10-10-2013_04-07-databases.gz*
> - *psql -U postgres -f postgresql-all-10-10-2013_04-07-databases
> postgres*
>
> ** **
>
> The above works between two different servers that both run 8.1.****
>
> ** **
>
> What am I missing to restore all my databases with tables and data from
> 8.1 to the new 8.4?****
>
> ** **
>
> Thanks****
>
> ** **
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: move dbs from 8.1 to 8.4
@ 2013-10-10 20:53 Marc Fromm <Marc.Fromm@wwu.edu>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 0 replies; 6+ messages in thread
From: Marc Fromm @ 2013-10-10 20:53 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
I also realized that during the restore any database with latin1 encoding is not created at all
psql:pgdbs:167: ERROR: encoding LATIN1 does not match locale en_US.UTF-8
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
About half my databases are latin1.
My problem is now two fold, how do I migrate the databases with their tables and data and migrate the latin1 encoded databases?
Thanks
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Marc Fromm
Sent: Thursday, October 10, 2013 1:17 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] move dbs from 8.1 to 8.4
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the databases from the old server running fedora and postgresql 8.1 using this script.
#!/bin/bash
# Backup all Postgresql databases
# Location of the backup logfile.
logfile="/var/lib/pgsql/backups/logs/pg_back.log"
# Location to place backups.
backup_dir="/var/lib/pgsql/backups"
touch $logfile
timeslot=`date +%m-%d-%Y_%H-%M`
timeinfo=`date '+%T-%F'`
/usr/bin/vacuumdb -azh 127.0.0.1 -U postgres
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-all-$timeslot-databases.gz"
echo "Backup of all databses complete at $timeinfo for time slot $timeslot--pg_dumpall." >> $logfile
The new server is running postgresql 8.4.
When I restore the databases, with the commands below, only the databases are created, no tables and no data is restored.
* gunzip postgresql-all-10-10-2013_04-07-databases.gz
* psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres
The above works between two different servers that both run 8.1.
What am I missing to restore all my databases with tables and data from 8.1 to the new 8.4?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: move dbs from 8.1 to 8.4
@ 2013-10-10 20:54 Steve Crawford <scrawford@pinpointresearch.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
2 siblings, 2 replies; 6+ messages in thread
From: Steve Crawford @ 2013-10-10 20:54 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; pgsql-admin
On 10/10/2013 01:17 PM, Marc Fromm wrote:
>
> I built a new server running centos 6.4 and postgresql 8.4. I backed
> up all the databases from the old server running fedora and postgresql
> 8.1 using this script.
>
> *#!/bin/bash*
>
> *# Backup all Postgresql databases*
>
> **
>
> *# Location of the backup logfile.*
>
> *logfile="/var/lib/pgsql/backups/logs/pg_back.log"*
>
> **
>
> *# Location to place backups.*
>
> *backup_dir="/var/lib/pgsql/backups"*
>
> *touch $logfile*
>
> *timeslot=`date +%m-%d-%Y_%H-%M`*
>
> *timeinfo=`date '+%T-%F'`*
>
> **
>
> */usr/bin/vacuumdb -azh 127.0.0.1 -U postgres*
>
> */usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip >
> "$backup_dir/postgresql-all-$timeslot-databases.gz"*
>
> *echo "Backup of all databses complete at $timeinfo for time slot
> $timeslot--pg_dumpall." >> $logfile*
>
> The new server is running postgresql 8.4.
>
> When I restore the databases, with the commands below, only the
> databases are created, no tables and no data is restored.
>
> o *gunzip postgresql-all-10-10-2013_04-07-databases.gz*
> o *psql -U postgres -f postgresql-all-10-10-2013_04-07-databases
> postgres*
>
> The above works between two different servers that both run 8.1.
>
> What am I missing to restore all my databases with tables and data
> from 8.1 to the new 8.4?
>
> Thanks
>
It would be helpful to see any error messages from the client or the
log. But lacking that info, my first suggestion is to be sure that you
are using pg_dumpall from the *new* version of PostgreSQL and *not*
trying to restore a dump taken with an old version into the new version
of the database. It's not clear which versions of the clients you are
using from your message but I'm guessing this may be part of your issue.
The second suggestion is to upgrade to a recent version of PostgreSQL.
If you are going to the effort of an upgrade it seems like a bit of a
waste of time to go to 8.4 which is only supported for a few more months
when you could move to 9.3 and have a version supported for nearly five
more years plus all the feature and performance improvements that have
been made in the several years since 8.4 was released.
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: move dbs from 8.1 to 8.4
@ 2013-10-10 21:07 Marc Fromm <Marc.Fromm@wwu.edu>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 6+ messages in thread
From: Marc Fromm @ 2013-10-10 21:07 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-admin
On the old system running 8.1 I am executing the pg_dumpall command as shown in the script. I then copy the file to the new server running 8.4 and run this command "psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres"
The log is reporting
database name_of_database does not exist
create database name_of_database
There is an error reporting for latin1 encoded databases :
psql:pgdbs:167: ERROR: encoding LATIN1 does not match locale en_US.UTF-8
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8.
Any latin1 encoded databases do not get created.
Thanks
I am using 8.4 just because it's what gets installed with CentOS6.4.
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Thursday, October 10, 2013 1:55 PM
To: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
On 10/10/2013 01:17 PM, Marc Fromm wrote:
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the databases from the old server running fedora and postgresql 8.1 using this script.
#!/bin/bash
# Backup all Postgresql databases
# Location of the backup logfile.
logfile="/var/lib/pgsql/backups/logs/pg_back.log"
# Location to place backups.
backup_dir="/var/lib/pgsql/backups"
touch $logfile
timeslot=`date +%m-%d-%Y_%H-%M`
timeinfo=`date '+%T-%F'`
/usr/bin/vacuumdb -azh 127.0.0.1 -U postgres
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-all-$timeslot-databases.gz"
echo "Backup of all databses complete at $timeinfo for time slot $timeslot--pg_dumpall." >> $logfile
The new server is running postgresql 8.4.
When I restore the databases, with the commands below, only the databases are created, no tables and no data is restored.
* gunzip postgresql-all-10-10-2013_04-07-databases.gz
* psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres
The above works between two different servers that both run 8.1.
What am I missing to restore all my databases with tables and data from 8.1 to the new 8.4?
Thanks
It would be helpful to see any error messages from the client or the log. But lacking that info, my first suggestion is to be sure that you are using pg_dumpall from the *new* version of PostgreSQL and *not* trying to restore a dump taken with an old version into the new version of the database. It's not clear which versions of the clients you are using from your message but I'm guessing this may be part of your issue.
The second suggestion is to upgrade to a recent version of PostgreSQL. If you are going to the effort of an upgrade it seems like a bit of a waste of time to go to 8.4 which is only supported for a few more months when you could move to 9.3 and have a version supported for nearly five more years plus all the feature and performance improvements that have been made in the several years since 8.4 was released.
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: move dbs from 8.1 to 8.4
@ 2013-10-10 21:51 Marc Fromm <Marc.Fromm@wwu.edu>
parent: Steve Crawford <scrawford@pinpointresearch.com>
1 sibling, 0 replies; 6+ messages in thread
From: Marc Fromm @ 2013-10-10 21:51 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; +Cc: pgsql-admin
After the last database is created there is a failed connection attempt to the first database, which never gets created since it is latin1 encoding.
psql:pgdbs:229: ERROR: database "wsasea_db" does not exist
CREATE DATABASE
psql:pgdbs:233: \connect: FATAL: database "awarding_letter_count" does not exist
I wonder if this connect: Fatal message is what stops the population of the tables and data, since on the server running 8.1, after the databases are created the tables and data are then populated.
From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Thursday, October 10, 2013 1:55 PM
To: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
On 10/10/2013 01:17 PM, Marc Fromm wrote:
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the databases from the old server running fedora and postgresql 8.1 using this script.
#!/bin/bash
# Backup all Postgresql databases
# Location of the backup logfile.
logfile="/var/lib/pgsql/backups/logs/pg_back.log"
# Location to place backups.
backup_dir="/var/lib/pgsql/backups"
touch $logfile
timeslot=`date +%m-%d-%Y_%H-%M`
timeinfo=`date '+%T-%F'`
/usr/bin/vacuumdb -azh 127.0.0.1 -U postgres
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-all-$timeslot-databases.gz"
echo "Backup of all databses complete at $timeinfo for time slot $timeslot--pg_dumpall." >> $logfile
The new server is running postgresql 8.4.
When I restore the databases, with the commands below, only the databases are created, no tables and no data is restored.
* gunzip postgresql-all-10-10-2013_04-07-databases.gz
* psql -U postgres -f postgresql-all-10-10-2013_04-07-databases postgres
The above works between two different servers that both run 8.1.
What am I missing to restore all my databases with tables and data from 8.1 to the new 8.4?
Thanks
It would be helpful to see any error messages from the client or the log. But lacking that info, my first suggestion is to be sure that you are using pg_dumpall from the *new* version of PostgreSQL and *not* trying to restore a dump taken with an old version into the new version of the database. It's not clear which versions of the clients you are using from your message but I'm guessing this may be part of your issue.
The second suggestion is to upgrade to a recent version of PostgreSQL. If you are going to the effort of an upgrade it seems like a bit of a waste of time to go to 8.4 which is only supported for a few more months when you could move to 9.3 and have a version supported for nearly five more years plus all the feature and performance improvements that have been made in the several years since 8.4 was released.
Cheers,
Steve
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-10-10 21:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-10 20:17 move dbs from 8.1 to 8.4 Marc Fromm <Marc.Fromm@wwu.edu>
2013-10-10 20:52 ` Payal Singh <payal@omniti.com>
2013-10-10 20:53 ` Marc Fromm <Marc.Fromm@wwu.edu>
2013-10-10 20:54 ` Steve Crawford <scrawford@pinpointresearch.com>
2013-10-10 21:07 ` Marc Fromm <Marc.Fromm@wwu.edu>
2013-10-10 21:51 ` Marc Fromm <Marc.Fromm@wwu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox