I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.
From what I've read, and it could be wrong, finding a good PostgreSQL blog has been challenging so please feel free to recommend some to me, I need to figure out how this app works so I can have trust in my backups and Slony replication. I had a dev restore a backup I took from PgadminIII via 'custom', 'directory', and 'tar' format while selecting 'OIDs' but he said 2 of them didn't load, tar did but it was only the directory, not the data. I'm really confused now.
I am using PGAdminIII, it has a pg_dump and pg_dumpall option. I want to back everything up that I need to test restore this database somewhere and verify that yes, all the data we need and our backup is good. Eventually I want to write an auto-restore script but 1 day at a time. pg_dumpall apparently has a -globals command that's supposed to backup everything, but the help for pg_dumpall shows a "-g, --globals-only dump only global objects, no databases", not a --globals option.
I thought pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'. According to: http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even with pg_dumpall I need to use a -o option to backup foreign keys, I can't really imagine when I wouldn't want to backup foreign keys and this would make more sense as a default options.
How would I take care of orphaned users and validate I have everything? I'd like to actually restore my backup file on another server and verify everything works. If anyoen has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful. Thank you.
I hope I don't sound like some elitist but thus far I'm very disapointed with PostgreSQL backups, most likely it has to do with being a total PostgreSQL noob than anything else. I had a PostgreSQL server but I still can't fathom why the app would not backup OIDs by default! It seems like 99.9% of the time you would want that. Thanks.
UPDATE 1: Quick update: The postgres sites write up on http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html mentions that the globals option I was looking for seems to be a default option on this version, but it still needs the -o command. If someone can verify or give me an example command to restore a single database elsewhere with everything it needs I'd appreciate it. Thanks!