Tools Index

Postgresql

1. Install Postgresql

Install postgresql;

        $ prt-get depinst postgresql
        
        # mkdir /srv/pgsql/
        # touch /var/log/postgresql
        # chown postgres:postgres /srv/pgsql /var/log/postgresql
        # sudo -u postgres initdb -D /srv/pgsql/data
        

2. Configure Server

2.1. Init script

Change /etc/rc.d/postgresql;

        #
        # /etc/rc.d/postgresql: start, stop or restart PostgreSQL server postmaster
        #

        PG_DATA=/srv/pgsql/data

        case "$1" in
            start|stop|status|restart|reload)
                sudo -u postgres pg_ctl -D "$PG_DATA" -l /var/log/postgresql "$1"
                ;;
            *)
                echo "usage: $0 start|stop|restart|reload|status"
                ;;
        esac

        # End of file
        

2.2. Certificates

        $ sudo openssl genrsa -des3 -out /etc/ssl/keys/pg.key 2048
        Password:
        Generating RSA private key, 2048 bit long modulus
        ..............................+++
        ............+++
        e is 65537 (0x10001)
        Enter pass phrase for /etc/ssl/keys/pg.key:
        Verifying - Enter pass phrase for /etc/ssl/keys/pg.key:
        

Create ceritificate signing request. For "Common Name" provide domain name or ip address, leave challange password and optional company name blank;

        $ sudo openssl req -x509 -in server.req -text -key /etc/ssl/keys/pg.key -out /etc/ssl/certs/pg.crt

        Enter pass phrase for /etc/ssl/keys/pg.key:
        You are about to be asked to enter information that will be incorporated
        into your certificate request.
        What you are about to enter is what is called a Distinguished Name or a DN.
        There are quite a few fields but you can leave some blank
        For some fields there will be a default value,
        If you enter '.', the field will be left blank.
        -----
        Country Name (2 letter code) [AU]:PT
        State or Province Name (full name) [Some-State]:
        Locality Name (eg, city) []:
        Organization Name (eg, company) [Internet Widgits Pty Ltd]:
        Organizational Unit Name (eg, section) []:
        Common Name (e.g. server FQDN or YOUR name) []:machine.example.org
        Email Address []:

        Please enter the following 'extra' attributes
        to be sent with your certificate request
        A challenge password []:
        An optional company name []:
        $
        

Having password is a good idea, but requires it every time pg is restarted. To remove;

        $ sudo cp /etc/ssl/keys/pg.key /etc/ssl/keys/pg.key.pass
        $ sudo openssl rsa \
            -in /etc/ssl/keys/pg.key.pass \
            -out /etc/ssl/keys/pg.key
        
        Enter pass phrase for /etc/ssl/keys/pg.key.pass:
        writing RSA key
        
        $ sudo chown postgres:postgres /etc/ssl/keys/pg.key*
        $ sudo chmod 0600 /etc/ssl/keys/pg.key*
        $ sudo chmod 644 /etc/ssl/certs/pg.cert
        

2.3. Super user password

Create password for super user;

        # su postgres
        $ psql -U postgres
        

2.4. Configure postgresql.conf

Edit /srv/pgsql/data/postgresql.conf;

        # - Security and Authentication -

        #authentication_timeout = 1min          # 1s-600s
        ssl = on                                # (change requires restart)
        #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                                # (change requires restart)
        #ssl_prefer_server_ciphers = on         # (change requires restart)
        #ssl_ecdh_curve = 'prime256v1'          # (change requires restart)
        ssl_cert_file = '/etc/ssl/certs/pg.crt' # (change requires restart)
        ssl_key_file = '/etc/ssl/keys/pg.key'   # (change requires restart)
        #ssl_ca_file = ''                       # (change requires restart)
        #ssl_crl_file = ''                      # (change requires restart)
        password_encryption = scram-sha-256
        

2.5. Configure pg_hba.conf

Edit /srv/pgsql/data/pg_hba.conf;

        # TYPE  DATABASE        USER            ADDRESS                 METHOD
        local   postgres        all             trust
        host    postgres        all             127.0.0.1/32            trust
        host    all             all             127.0.0.1/32            scram-sha-256
        host all all 0.0.0.0/0 reject
        

Start server and alter postgres password

        # /etc/rc.d/postgresql start
        
        postgres=# alter user postgres with password 'new_password';
        

2.6. Configure syslog-ng

Configure Syslog-ng, check Michael at otacoo article. Example;

Edit /pgsql/data/postgresql.conf;

        log_destination = 'syslog' # Can specify multiple destinations
        syslog_facility='LOCAL0'
        syslog_ident='postgres'
        log_connections = on
        log_disconnections = on
        log_duration = on
        

Create /etc/logrotate.d/postgres;

        /var/log/pgsql {
            weekly
            compress
            delaycompress
            rotate 10
            notifempty
            create 660 postgres postgres
        }
        
        destination postgres { file("/var/log/pgsql"); };
        filter f_postgres { facility(local0); };
        log { source(s_log); filter(f_postgres); destination(postgres); };
        

3. Create User

Create a new user with createuser command;

        $ sudo -u postgres createuser --pwprompt --encrypted \
        --no-createrole --no-createdb flyspray
        Enter password for new user:
        Enter it again:
        

4. Create Database

Create a new database for new user with createdb command;

        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
        --owner=flyspray db_flyspray
        

5. Drop Database

Deleting database with dropdb command;

        sudo -u postgres dropdb db_flyspray
        

6. Drop User

Deleting user with dropuser command;

        sudo -u postgres dropuser flyspray
        

7. Psql

Lets check with psql, login with user postgres;

        $ sudo -u postgres psql
        

First show help;

        postgres=# \?
        

7.1. List Databases and Roles

List roles then list databases;

        postgres=# \dg
        postgres=# \l
        

7.2. Create Database

        postgres=# create database db_flyspray_ext owner flyspray encoding 'UTF-8' template template0;
        

7.3. Drop Tables

This example assumes that all tables, are in public schema. First connect to database and list tables;

        postgres=# revoke SELECT on db_flyspray from flyspray;
        
        postgres=# \c db_flyspray
        postgres=# \dt
        

Drop all tables on public schema and recreate public schema;

        db_flyspray=# drop schema public cascade;
        db_flyspray=# create schema public;
        

8. Backup and restore

8.1. Dump databases

        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
        

8.2. Restore

        $ gzip -c cluster_dump.gz | psql -U postgres 
        
Tools Index

This is part of the Hive System Documentation. Copyright (C) 2019 Hive Team. See the file Gnu Free Documentation License for copying conditions.