Tools Index

Postgresql

1.1. Install Postgresql

        $ prt-get depinst postgresql

Mount Point;

        # mkdir -p /srv/pgsql
        # mount /srv/pgsql

Create user;

        # useradd -U -m -d /srv/pgsql -s /bin/false postgres
        useradd: warning: the home directory already exists.
        Not copying any file from skel directory into it.

        # passwd -l postgres
        passwd: password expiry information changed.
        # touch /var/log/pgsql
        # chown -R postgres:postgres /srv/pgsql /var/log/pgsql
        # ldconfig /user/lib/postgresql

$ sudo -u postgres mkdir -p /srv/pgsql/data # sudo -u postgres touch /srv/pgsql/.psql_history

Create /etc/logrotate.d/postgres;

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

Edit /etc/rc.d/postgresql;

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

        PG_DATA=/srv/pgsql/data
        PG_HOME=/srv/pgsql

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

        # End of file

1.2. Configure Server

        # sudo -u postgres initdb -D /srv/pgsql/data

Create password for super user;

        # su postgres
        $ psql -U postgres

Edit /pgsql/data/postgresql.conf;

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

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

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

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

Start server and alter postgres password

        # /etc/rc.d/postgresql start

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

1.3. Create User

Create a new user with createuser command;

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

1.4. Create Database

Create a new database for new user with createdb command;

        $ sudo -u postgres createdb --template=template0 --encoding=UTF8 \
        --owner=laravel_user laravel_db

1.5. Drop Database

Deleting database with dropdb command;

        sudo -u postgres dropdb laravel_db

1.6. Drop User

Deleting user with dropuser command;

        sudo -u postgres dropuser laravel_user

1.7. Psql

Lets check with psql, login with user postgres;

        $ sudo -u postgres psql

First show help;

        postgres=# \?

Psql - List Databases and Roles

List roles then list databases;

        postgres=# \dg
        postgres=# \l

Psql - Create Database

        postgres=# create database laravel_db_ext owner laravel_user encoding 'UTF-8' template template0;

Psql - Drop Tables

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

        postgres=# \c laravel_db
        postgres=# \dt

Drop all tables on public schema and recreate public schema;

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

Backup

Backup Database

Restore

        $ psql laravel_db < database_dump

Tools Index