mu - Soul of a tiny new machine. More thorough tests → More comprehensible and rewrite-friendly software → More resilient society.

<!DOCTYPE html>
<html dir="ltr" lang="en">
    <head>
        <meta charset='utf-8'>
        <title>Postgresql</title>
    </head>
    <body>

        <a href="index.html">Tools Index</a>

        <h1>Postgresql</h1>


        <h2 id="install">1. Install Postgresql</h2>

        <p>Install postgresql;</p>

        <pre>
        $ prt-get depinst postgresql
        </pre>

        <pre>
        # mkdir /srv/pgsql/
        # touch /var/log/postgresql
        # chown postgres:postgres /srv/pgsql /var/log/postgresql
        # sudo -u postgres initdb -D /srv/pgsql/data
        </pre>

        <h2 id="config">2. Configure Server</h2>

        <h3>2.1. Init script</h3>

        <p>Change <a href="conf/etc/rc.d/postgresql">/etc/rc.d/postgresql</a>;</p>

        <pre>
        #
        # /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
        </pre>

        <h3>2.2. Certificates</h3>

        <pre>
        $ 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:
        </pre>

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

        <pre>
        $ 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) []:core.privat-network.net
        Email Address []:

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

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

        <pre>
        $ 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
        </pre>

        <pre>
        Enter pass phrase for /etc/ssl/keys/pg.key.pass:
        writing RSA key
        </pre>

        <pre>
        $ 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
        </pre>

        <h3>2.3. Super user password</h3>

        <p>Create password for super user;</p>

        <pre>
        # su postgres
        $ psql -U postgres
        </pre>

        <h3>2.4. Configure postgresql.conf</h3>

        <p>Edit <a href="conf/srv/pgsql/data/postgresql.conf">/srv/pgsql/data/postgresql.conf</a>;</p>

        <pre>
        # - 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 = on
        </pre>

        <h3>2.5. Configure pg_hba.conf</h3>

        <p>Edit
        <a href="conf/srv/pgsql/data/pg_hba.conf">/srv/pgsql/data/pg_hba.conf</a>;
        </p>

        <pre>
        # TYPE  DATABASE        USER            ADDRESS                 METHOD

        # "local" is for Unix domain socket connections only
        #local   all             all                                     trust
        local   all             postgres                                 ident
        # IPv4 local connections:
        host    all             all             127.0.0.1/32            trust
        #hostssl    all             all             192.168.0.0/32             md5
        # IPv6 local connections:
        #host    all             all             ::1/128                 trust
        # Allow replication connections from localhost, by a user with the
        # replication privilege.
        #local   replication     postgres                                trust
        #host    replication     postgres        127.0.0.1/32            trust
        #host    replication     postgres        ::1/128                 trust
        </pre>

        <p>Start server and alter postgres password</p>

        <pre>
        # /etc/rc.d/postgresql start
        </pre>

        <pre>
        postgres=# alter user postgres with password 'new_password';
        </pre>

        <h3 id="syslog">2.6. Configure syslog-ng</h3>

        <p><a href="syslog-ng.html">Configure Syslog-ng</a>, check <a href="http://michael.otacoo.com/postgresql-2/postgres-settings-simple-syslog-configuration-with-syslog-ng/">Michael at otacoo</a> article. Example;</p>

        <p>Edit /pgsql/data/<a href="../conf/srv/pgsql/data/postgresql.conf">postgresql.conf</a>;</p>

        <pre>
        log_destination = 'syslog' # Can specify multiple destinations
        syslog_facility='LOCAL0'
        syslog_ident='postgres'
        log_connections = on
        </pre>


        <p>Create /etc/logrotate.d/postgres;</p>

        <pre>
        /var/log/pgsql {
            weekly
            compress
            delaycompress
            rotate 10
            notifempty
            create 660 postgres postgres
        }
        </pre>


        <pre>
        destination postgres { file("/var/log/pgsql"); };
        filter f_postgres { facility(local0); };
        log { source(s_log); filter(f_postgres); destination(postgres); };
        </pre>


        <h2 id="createuser">3. Create User</h2>

        <p>Create a new user with createuser command;</p>

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

        <h2 id="createdb">4. Create Database</h2>

        <p>Create a new database for new user with createdb command;</p>

        <pre>
        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
        --owner=flyspray db_flyspray
        </pre>

        <h2 id="dropdb">5. Drop Database</h2>

        <p>Deleting database with dropdb command;</p>

        <pre>
        sudo -u postgres dropdb db_flyspray
        </pre>

        <h2 id="dropuser">6. Drop User</h2>

        <p>Deleting user with dropuser command;</p>

        <pre>
        sudo -u postgres dropuser flyspray
        </pre>

        <h2 id="psql">7. Psql</h2>

        <p>Lets check with psql, login with user postgres;</p>

        <pre>
        $ sudo -u postgres psql
        </pre>

        <p>First show help;</p>

        <pre>
        postgres=# \?
        </pre>

        <h3 id="listdb">7.1. List Databases and Roles</h3>

        <p>List roles then list databases;</p>

        <pre>
        postgres=# \dg
        postgres=# \l
        </pre>

        <h3 id="psqldb">7.2. Create Database</h3>

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

        <h3 id="droptables">7.3. Drop Tables</h3>

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

        <pre>
        postgres=# revoke SELECT on db_flyspray from flyspray;
        </pre>

        <pre>
        postgres=# \c db_flyspray
        postgres=# \dt
        </pre>

        <p>Drop all tables on public schema and recreate public schema;</p>

        <pre>
        db_flyspray=# drop schema public cascade;
        db_flyspray=# create schema public;
        </pre>

        <h3 id="backup">7.4. Backup</h3>


        <p>Backup Database</p>

        <h3 id="backup">7.5. Restore</h3>

        <pre>
        $ psql db_flyspray &lt; database_dump
        </pre>

        <a href="index.html">Tools Index</a>
        <p>
        This is part of the c9-doc Manual.
        Copyright (C) 2016
        c9 team.
        See the file <a href="../fdl-1.3-standalone.html">Gnu Free Documentation License</a>
        for copying conditions.</p>

    </body>
</html>