Switching over linux users to the postgres account and access a postgres prompt

$ sudo su - postgres or $ sudo -i -u postgres then $ psql

Accessing a postgres prompt without switching accounts

$ sudo -u postgres psql or $ psql -U my_db_user

Exit the interactive postgres session

postgres=# \q

Accessing postgres and connect to a database

$ psql -U my_db_user then postgres=# \c my_db or $ psql -U my_db_user -d my_db

Restarting postgres service

$ sudo service postgresql restart

Selecting roles

postgres=# select * from pg_roles;

Create a role

postgres=# CREATE ROLE my_demo_role WITH CREATEDB LOGIN ENCRYPTED PASSWORD 'my_password'; Notes about privilegies:

• SUPERUSER - A database SUPERUSER deserves a word of caution. Bottom line, roles with this attribute can create another SUPERUSER. Matter of fact, this attribute is required to create another SUPERUSER role. Since roles with this attribute bypass all permission checks, grant this privilege judiciously.

• CREATEDB - Allows the role to create databases.

• CREATEROLE - With this attribute, a role can issue the CREATE ROLE command. Hence, create other roles.

• LOGIN - Enables the ability to login. A role name with this attribute can be used in the client connection command. More info about privilegies here... More info about roles here...

Delete a role

postgres=# DROP ROLE demo_role;

Change user password

postgres=# ALTER USER engenhoapp_dev WITH CREATEDB LOGIN ENCRYPTED PASSWORD ‘fe375500’';

Create DB, connect and quit

postgres=# CREATE DATABASE db_demo; postgres=# \c db_demo postgres=# \q

Rename database

postgres=# ALTER DATABASE db_to_change RENAME TO new_name;

To rename a database, you have to connect to another database e.g., postgres.

Change owner

postgres=# ALTER DATABASE db_to_change OWNER TO new_onwer;

Only the superuseror owner of the database can change the database’s owner. The database owner must also have the CREATEDB privilege to rename the database.

Check database encoding

my_database -c 'SHOW SERVER_ENCODING';

Change tablespace

To change the default tablespace of the database, you use ALTER DATABASE SET TABLESPACE as follows:

postgres=# ALTER DATABASE db_name SET TABLESPACE new_tablespace;

The statement moves tables and indexes from the legacy tablespace to the new one.

Create a table

postgres=# 
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

More info here...

Select db owner

postgres=# 
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'your_name'
ORDER BY 1;