Backup and Restore PostgreSQL database dump in Ubuntu
Posted by Vivek Shukla on Jun 20, 2023 under PostgreSQL
Learn how to backup and restore PostgreSQL database dump in Ubuntu (Linux) and Mac.
Table of Contents
Taking backup/dump of the database:
pg_dump -U db_user database_name > database_dump_name.sql
Restoring backup dump
Creating new user
CREATE USER your_user_name WITH PASSWORD 'your_user_password';
Creating new database
It is better to switch to new user and create the new database, this way you won’t have to give permission explicitly.
CREATE DATABASE your_db_name;
But for some reason you didn’t/couldn’t create the database with new user then go to the last section which has all the commands to give user full access to the database.
Restore using psql
psql -U your_user_name -d your_db_name < dump_name.sql
Give Full Access to Database
If you haven’t created database with your new user then you’ll have to give it full permission so that it can perform the operations.
GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_user_name;
# change owner
ALTER DATABASE your_db_name OWNER TO your_user_name;
# allow user to connect
GRANT CONNECT ON DATABASE your_db_name TO your_user_name;
# grant public schema access
GRANT USAGE ON SCHEMA public TO your_user_name;
# grant access to all tables and sequences in schma public
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user_name;
Reference: https://www.postgresql.org/docs/current/backup.html