Backing up PostgreSQL users and groups (pg_dump and pg_dumpall)

hey this is darren from postgrescorps.com and today what i wanted to share is the solution to a pretty simple problem that happens pretty frequently it's not really a problem it's just better understanding by design what a pg dump does a common question we get is hey why aren't my users showing up when i use a pg dump for a backup so just to illustrate the problem i'm going to demonstrate it first over here on my default cluster this machine is called node 1 and i have a cluster running on the standard port of 5432 so on this cluster i have a database called organ if i were to log into oregon look at a table named employees then there is some data so let's say i want to do a backup of that database well what might i experience i just want to demonstrate one other thing on this node you'll notice i have several users that have been created even a couple that belong to groups so i have this enterprise group with data picard and riker i have a number of dba users one dba belongs to the db creator use group and so on if i list my databases you can see that this organ database i'm working with is actually owned by the dba user so what is the potential problem that people often encounter let's go back out and let's just execute a quick pg dump to make a backup of this database all right so i'll make a backup of the organ database using the pg dump utility let's see what that backup file looks like the cool thing about just the default text settings for a pg dump is that that file is human readable so as i go through and i see what postgres will do when it attempts to restore this database you can see i have all my settings being made here for the client up front then i get into some ddl to create extensions and then to create a table notice this statement here it's going to give ownership of the employees table to the dba user so let's attempt to create that and restore that on a cluster where the dba user doesn't exist i have another cluster set up on this machine it's running on port 5434. i do a listing then it's just like a brand new cluster all i've ever done here is running it db so it's brand new and empty it just has those default postgres databases that you see there same thing for my users and groups there's nothing there but the postgres user so if i were to follow the standard procedure to restore a pg dump it'll be something like this i'll create a database named oregon connect to organ and then let's do an import i'm going to import that file temp organ.sql and that i think that import messed up my uh my search path so then there's my employees table right and the ownership is a postgres user you'll recall that that really should be the dba user and we can see the error that occurred to indicate hey that dba user doesn't even exist so it really makes sense that people would ask the question well how do i get my users into pgdom the answer is well you basically don't when i go back to the postgres database i'm going to drop the database organ we're going to start again all right the solution is to do a pg dump all so i'm going to just run the pg dump all utility against the cluster and i'm going to send the output to a file called temp all.sql what is the difference between pg dump all and pg dump well pg don't ball is actually pretty interesting because if we take a look at the file generated there you'll notice at the very top instead of saying it's a postgres database dump it's postgres database cluster dump meaning that for this instance of postgres everything got dumped don't be confused by the term cluster that's postgres terminology for a database cluster which is essentially one single instance of postgres all right so we see the same settings taking place there's a whole bunch of users being created those users being created include their hashed passwords you can even see the roles that can't log in which function as groups and altering those users and groups so that here adding the memberships so that the users belong to the correct groups then postgres goes ahead and will create the databases and then one at a time it'll connect to any database i have here in this database dump so i could forward search through this file and i could look for any connect statement it's going to connect to baltimore and it's going to connect to a database named darren a database named films a database named oregon previously remember we just updated and or we just ran a dump on the organ database now this is a pg dump all is giving me everything but really all i'm really interested in is the stuff at the top these users i wanted so how can i get just the users actually pretty simple i'm going to run that pg dump all command i'm going to add one simple flag which is minus g minus g option stands for global objects in the postgres environment the users and groups don't reside in any one particular database instead they reside at the cluster level so that's why we have to use the pg dump all utility because it dumps all the contents from a cluster if i want to modify its behavior to not dump the actual data but just to give me the global objects i can run a command like that and then when i take a look at all that sql what do i have i have my users my global objects and then that's it there's no data after that if i want to restore that now to my uh other cluster i can first import the temp all that sql file again error that the postgres user already exists that's not a big deal now you can see i have all that structure again including group ownership for all those users and now if i do create database organ i connect to oregon and i import my organ backup now no error occurs to show that there's any problem with the dba user and again for some reason my search path got messed up a little bit but i'll just fix that up now you can see that employees table did get correctly imported with the correct ownership and all the data is there so again just to recap if you're working with postgres and you want to do a single database dump your correct utility is pg dump however when you take a look at a beginning of a pg dump it will start to create objects for that database it'll assign ownership but if these users don't exist then you'll start to get errors to get those users remember users are global objects in postgres that means they reside at the cluster level so you'll need to use the pg dump all utility to dump all cluster contents and to specifically only get the global objects the users out of there you can add the mysg option and your backup will look something like this hopefully this helps when you're working with postgres if you want more information about how to work this in production or if you have any questions please put it down in the comments like and subscribe and i hope to see you again here on this channel or even in one of our classes

Channel: PostgresCourse.com

Views: 2,369

Keywords: postgres, postgresql, backup, logical backup, database, pg_dump, pg_dumpall, database backup

Id: wM9M76ZWFcc

Length: 9min 24sec (564 seconds)

Published: Wed Jan 27 2021