Difference between revisions of "Postgres"
| Line 2: | Line 2: | ||
==Backup / Restore DB== | ==Backup / Restore DB== | ||
| + | ===backup=== | ||
This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up. | This will run the dump as the postgres user via su, and avoids some of the permissions errors that can creep up. | ||
# cd /tmp | # cd /tmp | ||
| Line 9: | Line 10: | ||
pg_dump -U username -h localhost databasename >> sqlfile.sql | pg_dump -U username -h localhost databasename >> sqlfile.sql | ||
ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file | ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file | ||
| − | + | ===restore=== | |
Now, with that file | Now, with that file | ||
# su -c 'psql --set ON_ERROR_STOP=on -f ./mydb_dump_2020.sql' postgres | # su -c 'psql --set ON_ERROR_STOP=on -f ./mydb_dump_2020.sql' postgres | ||
Revision as of 04:42, 28 June 2020
Backup / Restore DB
backup
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
ref: https://stackoverflow.com/questions/37984733/postgresql-database-export-to-sql-file
restore
Now, with that file
# su -c 'psql --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.
Create a user
Note that if you have an existing db, you might want to add ownership to this user, or r/w permissions.
