PostgreSQL comes with two command-line utilities for dumping and then restoring a database — pg_dump(Backup Database) and pg_restore(Restore Database), respectively

— first switch to postgres user
# su – postgres

Postgresql command for backup database

pg_dump -v -d databasename > database-dump-filename.dump


pg_dump -U db_user -W -F t db_name > /tmp/dump_name.tar

Here we used the following options:
-U to specify which user will connect to the PostgreSQL database server.
-W or –password will force pg_dump to prompt for a password before connecting to the server.
-F is used to specify the format of the output file, which can be one of the following:
p – plain-text SQL script
c – custom-format archive
d – directory-format archive
t – tar-format archive

Postgresql Create New UTF-8 Database,

below command direct can fire from postgres user

createdb -E UTF8 -T template0 –locale=en_US.utf8 <dbname>

To create database from psql terminal

CREATE DATABASE “snippetbucket_db”
WITH OWNER “postgres”
LC_CTYPE = ‘en_US.UTF-8’
TEMPLATE template0;

Are you looking for database restoration ! Let’s go

Postgresql command to restore database

pg_restore -d databasename database-dump-filename.dump

With pg_restore you have various options available, for example:

-C to create a database before restoring into it,
-c to drop database objects before recreating them,
-e exit if an error has encountered,
-F format to specify the format of the archive.

Restore a database with psql

psql -U db_user db_name < /tmp/dump_name.sql

Odoo: Restore manual dump backup.

pg_restore -v -O -x -n public -d oe12_sb_2019_second sb-2019-5-20.dump

database: oe12_sb_2019_second
dump file: sb-2019-5-20.dump

-v -O -x -n public these are key important parameters to restore database.