Some useful postgresql commands

Login to Postgresql
sudo -u postgres psql [db_name]

Get PostgreSQL Version information
postgres=# SELECT version();

Create a new role with password and assign it as a supper role
postgres=# CREATE USER openerp WITH PASSWORD 'openerp';

Change the password for users
ALTER USER davide WITH PASSWORD 'hu8jmn3';

Grant a PostgreSQL user as a super user:
postgres=# ALTER USER openerp WITH SUPERUSER;

Linux command to create postgresql user with super database user role:
# sudo -u postgres createuser openerp -s

Create a new database with UTF-8 encoding
sudo -u postgres createdb mydb -E UTF-8 -T template0

Backup a db
sudo -u postgres pg_dump -o db_name > bk_name

Restore a db
psql db_name < bk_name

Drop a database
postgres=# DROP DATABASE db_name;

Change database owner
postgres=# ALTER DATABASE target_database OWNER TO new_onwer;

List all databases
postgres=# \l

List all postgresql users
postgres=#\du

COMMAND LINE: Clone a database
# sudo -u postgres createdb -O ownername -T originaldb newdb


Access Postgresql database with Postgres user without password:

# sudo -u postgres psql postgres