about summary refs log blame commit diff stats
path: root/tools/postgresql.html
blob: 285e7c3d725e22cd07bb74acdb870be366d79d79 (plain) (tree)
1
2
3
4
5
6
7
8
9
10
11
12
13












                                            
                                                   
 
                                  

             
                                    

              
             



                                                                

              



                                                
                                                                                  






                                                                                   

                    





                                                                                 




                     
































                                                                                                          
                                                                          






























                                                                







                                              













                                                                                                      
                                                                           

                                                                           
                                           



                                           


                                                                                  


                                                                              



                                                                                     

              









                                                                    
                                                     
 

                                                                                                                                                                                                                             

                                                                                                     
             



                                                                      

                               

              
                                                

             







                                        

              
 
             


                                                                          

              

                                               




                                                              
                                              



                                    
                                                 




                                                                          
                                    

              
                                             



                                                     
                                           

              
                                           



                                                   
                                          

              
                                  












                                                              
                                                          







                                              
                                                 

             
                                                                                                      

              
                                                 





                                                           




                                                              





                                                                           

                                                 

              
                                                  
 
                                    
 


                                                         
 
                             

             
                                                     



                                            
                                                      
                          
                  

                                                                                            

           
<!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) []: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 []:
        $
        </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 = scram-sha-256
        </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   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
        </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
        log_disconnections = on
        log_duration = 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>

        <h2 id="backup">8. Backup and restore</h3>

        <h3>8.1. Dump databases</h3>

        <pre>
        $ pg_dumpall -U postgres | gzip > cluster_dump.gz
        </pre>

        <h3>8.2. Restore</h3>

        <pre>
        $ gzip -c cluster_dump.gz | psql -U postgres 
        </pre>

        <a href="index.html">Tools Index</a>
        <p>
        This is part of the Hive System Documentation.
        Copyright (C) 2019
        Hive Team.
        See the file <a href="../fdl-1.3-standalone.html">Gnu Free Documentation License</a>
        for copying conditions.</p>
    </body>
</html>