Difference: SCSArchiverCSStudioPostgresInstallationSuse (r2 vs. r1)

SCSArchiver CSStudio Postgres Installation Suse

Introduction

Installation
install
1. As as root
zypper install postgresql postgresql-jdbc postgresql-server 
Results in ...

 Loading repository data...
 Reading installed packages...
 Resolving package dependencies...
 
 The following 7 NEW packages are going to be installed:
 libpq5 postgresql postgresql94 postgresql94-server postgresql-init postgresql-jdbc postgresql-server
 
 7 new packages to install.
 Overall download size: 5.2 MiB. Already cached: 0 B. After the operation, additional 21.8 MiB will be used.
 Continue? [y/n/? shows all options] (y): y
 Retrieving package postgresql jdbc-9.4-3.2.noarch (1/7), 587.9 KiB (617.4 KiB unpacked)
 Retrieving: postgresql-jdbc-9.4-3.2.noarch.rpm ......................................................[done]
 Retrieving package libpq5-9.4.6-4.1.x86_64                            (2/7), 159.5 KiB (464.6 KiB unpacked)
 Retrieving: libpq5-9.4.6-4.1.x86_64.rpm .................................................[done (3.2 MiB/s)]
 Retrieving package postgresql-init-9.4-6.1.noarch                     (3/7),  11.0 KiB (  7.5 KiB unpacked)
 Retrieving: postgresql-init-9.4-6.1.noarch.rpm ......................................................[done]
 Retrieving package postgresql94-9.4.6-4.1.x86_64                      (4/7),   1.1 MiB (  4.2 MiB unpacked)
 Retrieving: postgresql94-9.4.6-4.1.x86_64.rpm .......................................................[done]
 Retrieving package postgresql94-server-9.4.6-4.1.x86_64               (5/7),   3.4 MiB ( 16.6 MiB unpacked)
 Retrieving: postgresql94-server-9.4.6-4.1.x86_64.rpm ....................................[done (6.6 MiB/s)]
 Retrieving package postgresql-9.4-1.1.noarch                          (6/7),   3.8 KiB (   83   B unpacked)
 Retrieving: postgresql-9.4-1.1.noarch.rpm ...........................................................[done]
 Retrieving package postgresql-server-9.4-1.1.noarch                   (7/7),   3.8 KiB (   83   B unpacked)
 Retrieving: postgresql-server-9.4-1.1.noarch.rpm ....................................................[done]
 Checking for file conflicts: ........................................................................[done]
 (1/7) Installing: postgresql-jdbc-9.4-3.2.noarch ....................................................[done]
 (2/7) Installing: libpq5-9.4.6-4.1.x86_64 ...........................................................[done]
 (3/7) Installing: postgresql-init-9.4-6.1.noarch ....................................................[done]
 Additional rpm output:
 Updating /etc/sysconfig/postgresql...
 			 
 (4/7) Installing: postgresql94-9.4.6-4.1.x86_64 .....................................................[done]
 (5/7) Installing: postgresql94-server-9.4.6-4.1.x86_64 ..............................................[done]
 (6/7) Installing: postgresql-9.4-1.1.noarch .........................................................[done]
 (7/7) Installing: postgresql-server-9.4-1.1.noarch ..................................................[done]
Then create the data directory:
mkdir -p /data/postgresql/9.4/data && 
chown postgres /data/postgresql/9.4/data
And change to user postgres
su - postgres 
Initialize Database Directory:
initdb -D /data/postgresql/9.4/data/ 
Results in ...

 The files belonging to this database system will be owned by user "postgres".
 This user must also own the server process.
 
 The database cluster will be initialized with locale "en_US.UTF-8".
 The default database encoding has accordingly been set to "UTF8".
 The default text search configuration will be set to "english".
 
 Data page checksums are disabled.
 
 fixing permissions on existing directory /data/postgresql/9.4/data ... ok
 creating subdirectories ... ok
 selecting default max_connections ... 100
 selecting default shared_buffers ... 128MB
 selecting dynamic shared memory implementation ... posix
 creating configuration files ... ok
 creating template1 database in /data/postgresql/9.4/data/base/1 ... ok
 initializing pg_authid ... ok
 initializing dependencies ... ok
 creating system views ... ok
 loading system objects' descriptions ... ok
 creating collations ... ok
 creating conversions ... ok
 creating dictionaries ... ok
 setting privileges on built-in objects ... ok
 creating information schema ... ok
 loading PL/pgSQL server-side language ... ok
 vacuuming database template1 ... ok
 copying template1 to template0 ... ok
 copying template1 to postgres ... ok
 syncing data to disk ... ok
 
 WARNING: enabling "trust" authentication for local connections
 You can change this by editing pg_hba.conf or using the option -A, or
 --auth-local and --auth-host, the next time you run initdb.
 
 Success. You can now start the database server using:
 
     postgres -D /data/postgresql/9.4/data
Exit back to superuser root:
exit 
Check and set boot option of service postgresql
chkconfig -l postgresql
postgresql                0:off  1:off  2:off  3:off  4:off  5:off  6:off
chkconfig postgresql on &&
chkconfig -l postgresql

Loading repository data...
Reading installed packages...
Resolving package dependencies...

The following 7 NEW packages are going to be installed:
libpq5 postgresql postgresql94 postgresql94-server postgresql-init postgresql-jdbc postgresql-server

7 new packages to install.
Overall download size: 5.2 MiB. Already cached: 0 B. After the operation, additional 21.8 MiB will be used.
Continue? [y/n/? shows all options] (y): y
Retrieving package postgresql jdbc-9.4-3.2.noarch (1/7), 587.9 KiB (617.4 KiB unpacked)
Retrieving: postgresql-jdbc-9.4-3.2.noarch.rpm ......................................................[done]
Retrieving package libpq5-9.4.6-4.1.x86_64                            (2/7), 159.5 KiB (464.6 KiB unpacked)
Retrieving: libpq5-9.4.6-4.1.x86_64.rpm .................................................[done (3.2 MiB/s)]
Retrieving package postgresql-init-9.4-6.1.noarch                     (3/7),  11.0 KiB (  7.5 KiB unpacked)
Retrieving: postgresql-init-9.4-6.1.noarch.rpm ......................................................[done]
Retrieving package postgresql94-9.4.6-4.1.x86_64                      (4/7),   1.1 MiB (  4.2 MiB unpacked)
Retrieving: postgresql94-9.4.6-4.1.x86_64.rpm .......................................................[done]
Retrieving package postgresql94-server-9.4.6-4.1.x86_64               (5/7),   3.4 MiB ( 16.6 MiB unpacked)
Retrieving: postgresql94-server-9.4.6-4.1.x86_64.rpm ....................................[done (6.6 MiB/s)]
Retrieving package postgresql-9.4-1.1.noarch                          (6/7),   3.8 KiB (   83   B unpacked)
Retrieving: postgresql-9.4-1.1.noarch.rpm ...........................................................[done]
Retrieving package postgresql-server-9.4-1.1.noarch                   (7/7),   3.8 KiB (   83   B unpacked)
Retrieving: postgresql-server-9.4-1.1.noarch.rpm ....................................................[done]
Checking for file conflicts: ........................................................................[done]
(1/7) Installing: postgresql-jdbc-9.4-3.2.noarch ....................................................[done]
(2/7) Installing: libpq5-9.4.6-4.1.x86_64 ...........................................................[done]
(3/7) Installing: postgresql-init-9.4-6.1.noarch ....................................................[done]
Additional rpm output:
Updating /etc/sysconfig/postgresql...

(4/7) Installing: postgresql94-9.4.6-4.1.x86_64 .....................................................[done]
(5/7) Installing: postgresql94-server-9.4.6-4.1.x86_64 ..............................................[done]
(6/7) Installing: postgresql-9.4-1.1.noarch .........................................................[done]
(7/7) Installing: postgresql-server-9.4-1.1.noarch ..................................................[done]
postgresql                0:off  1:off  2:off  3:on   4:off  5:on   6:off
  1. create data directory
Edit
mkdir -p /data/postgresql/9.4/data 
chown postgres /data/postgresql/9.4/data
/etc/sysconfig/postgresql
vi /etc/sysconfig/postgresql

Set POSTGRES_DATADIR
 #POSTGRES_DATADIR="~postgres/data"
 POSTGRES_DATADIR="/data/postgresql/9.4/data"
Change to user postgres ...
su - postgres 
... and edit the configuration file postgresql.conf:
  • listen_addresses
export PGDATA=/data/postgresql/9.4/data/ &&
vi $PGDATA/postgresql.conf

 #------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------
 
 # - Connection Settings -
 
 #listen_addresses = 'localhost'         # what IP address(es) to listen on;
 listen_addresses = '*'                  # what IP address(es) to listen on;
... followed by an edit of the file pg_hba.conf:
$PGDATA/pg_hba.conf 

 hostnossl archive archiver .gsi.de trust
 hostnossl archive report   .gsi.de trust
 #hostnossl all     all      192.168.*/16 trust
Back to superuser root:
exit 
Start postgresql as a service
service postgresql start 
Become again user postgres
su - postgres 
create test db and connect to it
createdb test &&
psql test

su - postgres 
initdb -D /data/postgresql/9.4/data/ 


The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/postgresql/9.4/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /data/postgresql/9.4/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

postgres -D /data/postgresql/9.4/data
exit 
  1. check and set boot option of service postgresql
chkconfig -l postgresql


postgresql                0:off  1:off  2:off  3:off  4:off  5:off  6:off
chkconfig postgresql on


chkconfig -l postgresql


postgresql                0:off  1:off  2:off  3:on   4:off  5:on   6:off
  1. edit /etc/sysconfig/postgresql
vi /etc/sysconfig/postgresql


bomb Language 'txt' not supported
 
            #POSTGRES_DATADIR="~postgres/data"
            POSTGRES_DATADIR="/data/postgresql/9.4/data"
    
su - postgres 
export PGDATA=/data/postgresql/9.4/data/
vi $PGDATA/postgresql.conf


bomb Language 'txt' not supported
 
            #------------------------------------------------------------------------------
            # CONNECTIONS AND AUTHENTICATION
            #------------------------------------------------------------------------------
            
            # - Connection Settings -
            
            #listen_addresses = 'localhost'         # what IP address(es) to listen on;
            listen_addresses = '*'                  # what IP address(es) to listen on;
         
$PGDATA/pg_hba.conf 


bomb Language 'txt' not supported
 
 		   hostnossl archive archiver .gsi.de trust
 		   hostnossl archive report   .gsi.de trust
 		   #hostnossl all     all      192.168.*/16 trust
         
exit 
  1. start
service postgresql start 
  1. create test db and connect to it
su - postgres 
createdb test
psql test

%FOREACH{"file" in="postgres_schema.txt"}% %FOREACH{"path" in="master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd"}% %FOREACH{"prefix" in="SCSArchiverCSStudioPostgreSql"}%

Configuration
Role/User/Permissions
Hostname Port lxhadeb05 5432
Database archive
      permissions
  user/role   read write admin
archiver pw: $archive choice-yeschoice-yeschoice-no
report pw: $report choice-yeschoice-nochoice-no
postgressuperuserchoice-yeschoice-yeschoice-yes

Database Configuration
Hostname lxhadeb05
Port 5432
Name archive
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Resulting psql User/Roles/Database setup
  • lxhadeb05
    • logged in to server as postgres superuser postgres
$ su - postgres
$ psql
# CREATE USER archiver WITH PASSWORD 'xxxxxx';
# CREATE USER report WITH PASSWORD 'yyyyyy';
# CREATE DATABASE archive OWNER archiver;
# SELECT * FROM pg_user;
# \q

Configuration - Schema
archive@lxhadeb05:5432

Hostname
lxhadeb05
Port
5432
Database
archive
Schema
archive
Scripts
    Create Database, Users, and Scheme

    • database_owner_ship provided:
      1. add client_encoding and SEARCH PATH
      2. if database does not exist, create it
      3. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all
    export PG_Owner=%pgOwner%
    export PG_Archiver=%userArchiver%
    export PG_Reporter=%userReport%
    export PG_Archiver_pw='%pwArchiver%' 
    export PG_Reporter_pw='%pwReport%' 
    export PGUSER= 
    export PGHOST=cbmdcs01 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    --- create database
    DO $$
    BEGIN
    IF NOT EXISTS( SELECT datname from pg_catalog.pg_database where datname = \'$PGDATABASE\' )
    THEN
    EXECUTE \'CREATE DATABASE $PGDATABASE;\'
    END IF;
    END
    $$;
    \connect $PGDATABASE;
    
    --- create roles / users
    
    DO $$
    BEGIN
    IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Archiver\' )
    THEN
    EXECUTE \'CREATE USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\';
    EXECUTE \'ALTER USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\';
    END IF;
    
    IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Reporter\' )
    THEN
    EXECUTE \'CREATE USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\';
    EXECUTE \'ALTER USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\';
    END IF;
    END
    $$;
    
    
    -- create scheme
    DO $$
    BEGIN
    IF NOT EXISTS( SELECT schema_name FROM information_schema.schemata WHERE schema_name = \'$schema\' )
    THEN
    EXECUTE \'CREATE SCHEMA $schema\';
    EXECUTE \'GRANT USAGE on SCHEMA $schema to $PGUSER\';
    END IF;
    END
    $$;
    --
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} && unset PGDATABASE && 
    psql -U PG_Owner -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -
    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all
    export PGUSER= 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=$css-address/raw/$path/$file 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -
      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER= 
      export PGHOST=lxhadeb05 
      export PGPORT=5432 
      export PGDATABASE=archive 
      schema=archive
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -
    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested
    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all
    export PG_Owner=
    export PGUSER= 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL="$css-address/raw/$path/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -
    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;
    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    --  and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h lxhadeb05 -p 5432 -U  -d archive -c "SELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', '', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).
    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.
    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all
    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER= 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=$css-address/raw/$path/$file 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 


woops, ordering error: got an ENDTWISTY before seeing a TWISTY

-- PeterZumbruch- 13 14 Jun 2016


-- PeterZumbruch - 16 Jun 2016

View topic | View difference side by side | History: r3 < r2 < r1 | More topic actions
 
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Hades Wiki? Send feedback
Imprint (in German)
Privacy Policy (in German)