PostgreSQL

Install

Debian/Ubuntu

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Remote access

1.- Modify pg_hba.conf to add Client Authentication Record
To allow remote access edit: /etc/postgresql/9.4/main/pg_hba.conf

sudo -i -u postgres -H nano /etc/postgresql/9.4/main/pg_hba.conf

Example:

 TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

2.- Change the Listen Address in postgresql.conf

sudo -i -u postgres -H nano /etc/postgresql/9.4/main/postgresql.conf

set: listen_addresses = '*'

Crate users

From postgres shell account type:

createuser --interactive

To change the user password:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'P@$$worD1';

Create database

From the postgres user shell:

createdb test1

CREATE TABLE / DROP TABLE

    CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

    DROP TABLE table_name;

List databases

\list or \l

List tables

\d
\dt

ADD, QUERY, DELETE from table

Add

INSERT INTO table_name (column_name1, column_name2, column_name3, column_name4) 
VALUES ('value1', 'value2', 'value3', 'value4');

Query

SELECT * FROM table_name;

Delete

DELETE FROM table_name WHERE column_name = 'value1';

Update

UPDATE table_name SET column_name1= 'value32' WHERE column_name2 = 'value2';

ALTER TABLE

ALTER TABLE table_name ADD column_name5 date;
ALTER TABLE table_name DROP column_name5;

Import .sql dump file

psql databasename < data_base_dump