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

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. Configure Server

Create password for super user;

        # su postgres
        $ psql -U postgres

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

        # TYPE  DATABASE        USER            ADDRESS                 METHOD

        # "local" is for Unix domain socket connections only
        #local   all             all                                     trust
        local   all             postgres                                 ident
        # IPv4 local connections:
        hostssl    all             all             127.0.0.1/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

Start server and alter postgres password

        # /etc/rc.d/postgresql start

        postgres=# alter user postgres with password 'new_password';

2.1. 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
        password_encryption=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;

7.4. Backup

Backup Database

7.5. Restore

        $ psql db_flyspray < database_dump

Tools Index