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-ow...@postgresql.org [mailto:pgsql-admin-ow...@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
