I'm moving my PG databases from one Linux server to another. I'm using the pg_dumpall command to dump all of my data from the first server and then using psql -f backup template1 postgres to restore the databases on the other server. One of the issues I'm having is that the encoding on the first is UNICODE, the second is UTF8 and I can't change it to UNICODE as it isn't available. Apparently most of the data is being restored when I restore the backup file on the second server. Some tables are blank, but there should be over 8,000 entries for one table in particular. I just can't get it the back and restore to work properly. Any suggestions?

*sigh* It seems in my haste I removed the original database from the first server. I now need to restore that database as well. I have multiple copies of several backups that I've made, but restoring them simply will not work correctly. Several tables are missing data completely where there should be thousands of rows of data.

Any help would be greatly appreciated.

I think I've fixed my problem, though it is far from ideal.

Out of the various backup files that I had, one used individual INSERT instead of COPY commands. I then broke my queries up in the order they needed to be executed in. One thing I don't understand is that the order the queries are listed will cause a failure no matter what. By this I mean, I have a many to many table with foreign keys for say user and email. The table contains user_id and email_id, which are dependent upon user.id and email.id, which are not inserted until further in the SQL file. This occurs many, many times since half, if not most, of the tables are many to many tables. Anyways, that's not the problem.

I organized my queries so that I could insert about 15,000 rows of data without the page timing out and that all relationships were properly established. By doing this oh so tedious task, I was able to restore all of my data.

Still though, it should not be this hard. I've never had this kind of issue with MySQL. Why does PostgreSQL have to make it so difficult?

I'm seeking proper guidance for this since I'm obviously not going about it the right way.