Postgresql: Basic Workflow

Category:

Create a role

Login to psql

psql -U postgres

Check existing roles

SELECT * FROM pg_roles;

Create the role

CREATE ROLE name WITH CREATEDB LOGIN PASSWORD 'secret'

Create a database

createdb -E UTF8 -U rolename dbname_production

Delete a database

dropdb -U postgres dbname_production

Restore database from sql file

psql -U rolename -d dvname_production < /root/db_backups/dbname.sql

Save database to sql file

pg_dump -U rolename dbname > filename.sql