Upgrading PostgreSQL 7.4 to 8.1.6

This past weekend I did some testing of exporting and importing databases between postgreSQL 7.4 and 8.1.6. I had to try several different methods before finding the correct way getting the data across. Outside of the Export and import process, I also want to change the character set that the database is encoded in. Apparently the default encoding set for 7.4 was SQL_ASCII and the standard encoding set for 8.1.6 is UTF8. Naturally the goal is get the final upgraded database in an UTF8 encoding.

This default encoding was the first stumbling block. I encountered several errors revolving around incorrect byte size. After I did some Googling found the answer.

The second roadblock was trying to figure out how to get from SQL_ASCII to UTF8. The pg_dump process using a plain text dump doesn’t work correctly between 7.4 and 8.1.6. The 8.1.6 kept giving a “did not find magic string in file header” message when I attempted the restore process. Or an encoding byte value error message while running the plain text dump through psql. These are the abbreviated steps That I’ve found that produce no errors.

pg_dump -f phpbb.dat -Fc phpbb
create database phpbb with encoding 'SQL_ASCII';
pg_restore -Fc -c -d phpbb phpbb.dat
pg_dump -f phpbb.dat -Fp phpbb
drop database phpbb;
create database phpbb;
psql phpbb < phpbb.dat

Dumping out in plain text from the 8.1.6 eliminates some of the indexing errors I received from a 7.4 plain text dump import into a UTF8 database.

When I do move the forums it shouldn’t take more than 20 minutes to go through the entire process. The other database is a different story.