Postgres Commands

postgres-commands.md

Lib

apt-get -y install postgresql postgresql-contrib

List all Databases

\list

All tables

\dt *
\d

Connect on other Databases

\connect db_production

Connect on a Database as a user

psql -U deploy -d db_production

Change the owner of Database

ALTER DATABASE db_production OWNER TO deploy;

Grant privileges on a database

GRANT ALL PRIVILEGES ON DATABASE db_production to deploy;

Grant privileges on a table

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO deploy;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO deploy;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO deploy;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO deploy;

Create User

sudo -u postgres createuser --superuser admin

Login

psql -U postgres
\du

Create Database

sudo -u postgres createdb -T template1 db_development

Plugins

sudo -u postgres psql template1 -c 'create extension if not exists "hstore"'

Drop

dropdb 'db_production'

Change Mode to "Password authentication"

sudo vim /etc/postgresql/9.3/main/pg_hba.conf

local   all             postgres                                peer

to

local   all             postgres                                trust

sudo service postgresql restart

su – postgres
psql
ALTER USER postgress with password 'password-new';
\q
create role deploy with createdb login password 'xxx';
\du

sudo -u postgres createdb -T template1 db_production

Login with a User

psql -U postgres

Login with a User on a Database with Password

psql -d db -U user -W

Change User password

ALTER USER user with password 'password-new';

sudo -u postgres psql
create user username with password 'password';
alter role username superuser createrole createdb replication;
create database projectname_production owner username;