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 developer restore a backup I took from PgadminIII via
tar format while selecting
OIDs but he said two 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_dumpalloption. 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 one day at a time.
pg_dumpall apparently has a
-globals option 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
pg_dumpall would at least backup foreign keys, but even that seems to be an 'option'. According to the documentation, 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 anyone has any suggestions on how to take a real backup in PostgreSQL and restore, I'd be very grateful.
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.
The Postgres documentation mentions that the
globals option I was looking for seems to be a default option on this version, but it still needs the
-o option. If someone can verify or give me an example command to restore a single database elsewhere with everything it needs I'd appreciate it.
Edit: being asked by site to show the uniqueness of this question by editing my question. This question raises the issue and get's clarity on OIDs in backups, the difference between globals and non globals, as well as testing restores recommendations to ensure the backup is good as opposed to just backing up. Due to the answers I was able to backup, figure out globals/oids, and started a test restore process nightly on Postgres using cron jobs. Thanks for the help!