From 07bedee34d9ded6f86904c7e4b4e02464ff8cb14 Mon Sep 17 00:00:00 2001 From: Silvino Silva Date: Thu, 15 Sep 2016 00:47:34 +0100 Subject: added tools --- tools/postgresql.html | 248 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 248 insertions(+) create mode 100644 tools/postgresql.html (limited to 'tools/postgresql.html') diff --git a/tools/postgresql.html b/tools/postgresql.html new file mode 100644 index 0000000..ee8a098 --- /dev/null +++ b/tools/postgresql.html @@ -0,0 +1,248 @@ + + + + + Postgresql + + + + 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 +

+ This is part of the c9-doc Manual. + Copyright (C) 2016 + Silvino Silva. + See the file Gnu Free Documentation License + for copying conditions.

+ + + + + -- cgit 1.4.1-2-gfad0