This almost works, but the restore has multiple errors when it tries to alter the newly built tables to the correct ownership. The error says that the specified role does not exist. Now, I don't do a lot of restores, but it seems logical to me that when the new db does not exist and you specify the -C flag to create it during the restore, then the dump script should have everything in it, including the new role, to make this work.
I know I could create the db manually and then do a createuser to get the proper role generated prior to the restore, but I shouldn't really have to do this, should I? Am I missing a flag in either the dump or the restore or both to make this succeed?
Now, I don't do a lot of restores, but it seems logical to me that when the new db does not exist and you specify the -C flag to create it during the restore, then the dump script should have everything in it, including the new role, to make this work.
pg_dump only dumps a single database. Users/Roles are so called "global objects" that are independent of a single database.
If you want to get all "global objects" but still only dump/restore a single database, you can use pg_dumpall to the the necessary SQL script for them:
and then run the SQL script prior to restoring the database with pg_restore.
An alternative would be to use pg_upgrade which will migrate a complete database cluster (aka "data dir") from one version to another. Additionally it will also be faster than the dump/restore thing. It could even do an "in-place" upgrade which is even more faster.
I was looking at the release notes for 9.3.3 and it suggests that some indexes might be corrupted during an upgrade, but I can't find if/where pg_upgrade is mentioned nor if it may leave such a problem. It'd be easy enough to drop the indexes and recreate them, but do you know if that would still be necessary?
I sincerely appreciate all your assistance, but I do have one final issue. I've have been looking all over the web and in the postgresql repositories for pg_upgrade and just cannot locate it. I did find a possible source download for it, but I'd prefer a binary for Centos 6.4. If worse came to worse, I could build it from souce, I'd just rather not do this as my upgrade procedure must later be run by QA people who don't do that sort of thing often.
Yes, I had looked in the /usr/pgsql-9.3/bin directory and pg_upgrade wasn't present. I did, however, issue another yum install postgresql93-contrib command and there it was. Maybe I fat-fingered the original installation, but anyway all is the right with the world now.
When downloading the binary bundles from EnterpriseDB this is included both in the Linux and the Windows archives. But apparently the packagers of your distribution chose to split it up in two different packages.