Backup / Restore DB
This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up.
# cd /tmp (just need a writeable directory, aka 777) # su -c 'pg_dump mydb >> mydb_dump_2020.sql' postgres
assuming you have a postgres admin user, with all rights, this will backup the db. If you are remote, you might need something similar to (localhost/ip, ports, etc specified):
pg_dump -U username -h localhost databasename >> sqlfile.sql
Create the new db:
su - postgres psql create database bbbtest; grant all privileges on database bbbtest to mypguser; ALTER DATABASE bbbtest OWNER TO mypguser;
and postgres permissions are a disaster online for getting info on (unforunately, there is nothing like mysql's mysql -uroot -p -e "grant all on db.* to 'user'@localhost identified by 'pass';"). But what does work is permissions for specific tables need to be enabled for the user, so:
GRANT ALL ON sometableindb TO mypguser; \quit exit
Now, with that file
# su -c 'psql -d newdbname --set ON_ERROR_STOP=on -f ./mydb_dump_2020.sql' postgres
If you get errors, adjust data as needed or remove the ON_ERROR_STOP (some can be ignored, some can not - use discretion). At least in some cases, you can move data without worrying about postgres versions. I executed a pg_dump from 11.# and imported to 9.6 without issue. Devuan Beowulf to Ascii.
TIP: if you don't specify -d, then the data will get uploaded to one of the preexisting DB. Not good.
-- su - postgres -- -- psql -- -- -- create database bbbtest; -- -- -- grant all privileges on database bbbtest to root; -- -- -- \quit -- -- exit -- psql bbbtest < dbmake.sql -- -- select * from A1;
Verify user permissions and upload was correct
su - mypguser psql select * from sometable;
See notes about table permissions above.
Create a user
Note that if you have an existing db, you might want to add ownership to this user, or r/w permissions.