PostgreSQL: Create Database, User and Grant Privileges

Open terminal and invoke this command:

psql -U postgres

to create new database, invoke this command:


to create new user, invoke this command:

CREATE USER dbuser WITH PASSWORD 'mypassword';

then, you can set your desired user-related preference like this

ALTER ROLE dbuser SET client_encoding TO 'utf8';
ALTER ROLE dbuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE dbuser SET timezone TO 'UTC';

to grant privileges, invoke this command:


Quit from PostgreSQL console


First Time with PostgreSQL – Creating Table

CREATE TABLE “public”.”product”
product_id integer NOT NULL,
product_name character varying,
supplier_name character varying,
unit_price integer

ALTER TABLE “public”.”product” ADD UNIQUE (“product_id”);
ALTER TABLE “public”.”product” ADD PRIMARY KEY (“product_id”);




PostgreSQL – Restore Backup with Command Line

There are two tools to look at, depending on how you created the dump file.

Your first source of reference should be the man page pg_dump(1) as that is what creates the dump itself. It says:

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

So depends on the way it was dumped out. You can probably figure it out using the excellent file(1)command – if it mentions ASCII text and/or SQL, it should be restored with psql otherwise you should probably use pg_restore

Restoring is pretty easy:

psql -U <username> -d <dbname> -1 -f <filename>.sql


pg_restore -U <username> -d <dbname> -1 -f <filename>.dump

Check out their respective manpages – there’s quite a few options that affect how the restore works. You may have to clean out your “live” databases or recreate them from template0 (as pointed out in a comment) before restoring, depending on how the dumps were generated.