PostgreSQL

Install

Debian/Ubuntu

sudo apt update
sudo apt install postgresql postgresql-contrib

Data directory

/var/lib/postgresql

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;

Drop all tables

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

List users

\du

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;

Dump database

g_dump -C -h localhost -U scrapy scrapydb > scrapydb_dump.sql

Dump one database to another database

g_dump -C -h localhost -U scrapy scrapydb | psql -h ec2-54-77-228-53.eu-west-1.compute.amazonaws.com -U scrapy scrapydb

Import .sql dump file

psql databasename < data_base_dump
pg_restore -h localhost -U postgres -d mapper -1 /tmp/mapper_before_refactoring_2018_11_13.sql

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

• Create the user
  

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

• Create the DB

postgres@hostname:~$ createdb databasename

CREATE DATABASE postgres@hostname:~$

• Grant access for the user to the DB

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';