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

Error creating UTF-8 database

02:39:35: Error: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) SUGERENCIA: Use the same encoding as in the template database, or use template0 as template.

Create user, database and password

The following step-by-step will let you create a user, a database (DB) and grant full access to the user to this DB.

All the commands are executed as the postgres privileged user. Create the user

For this, you use the command createuser which is provides with the postgreSQL package. Then answer the questions as you see fit :

postgres@hostname:~$ createuser

Enter name of role to add: username Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE postgres@hostname:~$ Create the DB

Use the createdb command to create the database :

postgres@hostname:~$ createdb databasename

CREATE DATABASE postgres@hostname:~$ Grand access for the user to the DB

And last, using the psql command, set a password for the user and grant accesses :

postgres@hostname:~$ psql
postgres=# alter user username with encrypted password 'password';

ALTER ROLE postgres=# grant all privileges on database databasename to username;

GRANT postgres@hostname:~$

Fix:

update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with template = template0 encoding = 'UTF8';
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';