Postgreql

This wiki is powered by ikiwiki.

Postgresql

CLuster loeschen

Datenbank stoppen
svcadm disable postgresql
/etc/init.d/postgreql stop

Cluster anlegen

mkdir -p /pgdata/<projekt>/<version>
initdb --pgdata /pgdata/<projekt>/<version> --encoding=UTF8 

Benutzer rw berechtigen

#!/bin/bash 
psql <<EOF
     grant all on database <db> to <rwuser>;
EOF

psql <database> <<EOF
     grant all on all tables    in schema <schema> to <rwuser>;
     grant all on all sequences in schema <schema> to <rwuser>;
EOF

Benutzer ro berechtigen

#!/bin/bash 

psql <database> <<EOF
     grant select on all tables    in schema <schema> to <rouser>;
     grant select on all sequences in schema <schema> to <rouser>;
EOF

Benutzer für Schema ändern

tList=$(psql -d aaa01 -qAt -c "select tablename from pg_tables where schemaname = '${SCHEMA}';")
for tbl in $tList ; do
psql -d aaa01 <<EOF
set search_path to '<SCHEMA>';
alter table ${tbl} owner to "<SCHEMA>";
EOF
done

sList=$(psql -d aaa01 -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = '${SCHEMA}';")

for seq in $sList ;do
psql -d aaa01 <<EOF
set search_path to 'ALKIS';
alter sequence $seq owner to "ALKIS"
EOF
done

Illumos

Data Verzeichnis in property Datenbank ändern

/usr/sbin/svccfg -v -s postgresql setprop config/data= astring: "/pgdata/webm/9.3/main"

Log verzeichnis in property Datenbank ändern

/usr/sbin/svccfg -v -s postgresql setprop config/log = astring: "/var/log/postgresql.log"

Werte in property Datenbank/restarter aktivieren

svcadm refresh postgresql:default

Datenbank stoppen

svcadm disable postgresql:default

/var/log für postgres beschreibbar machen

chmod 777 /var/log/

Datenbank starten

svcadm enable postgresql:default

Datenbank prüfen

svcs -xv 

Buch

PostgreSQL 9.0 High Performance

Performance

View pg_stat_bgwriter 
pg_statsinfo 

Analyse Skripts

https://github.com/pgexperts/pgx_scripts

Locks

   select a.locktype,a.database,a.relation,b.relname,a.mode,c.usename,a.pid,a.granted.
     from pg_locks  a ,
          pg_catalog.pg_statio_user_tables b , 
          pg_stat_activity c 
     where a.relation=b.relid 
           and a.pid=c.pid ;

pg_stat-bgwriter 

Abschalten  Reclaim Mode mit /proc/sys/vm/zone_reclaim_mode=0
transparent huge pages abschalten 
checkpoint intervalle kurz halten 

Replikation bdr/udr

postgresql.conf

shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_connections = 100
max_wal_senders = 10
max_replication_slots = 10
# Make sure there are enough background worker slots for BDR to run
max_worker_processes = 10

# These aren't required, but are useful for diagnosing problems
log_error_verbosity = verbose
log_min_messages = debug1
log_line_prefix = 'd=%d p=%p a=%a%q '

# Useful options for playing with conflicts
bdr.default_apply_delay=2000   # milliseconds
bdr.log_conflicts_to_table=on

pg_hba.conf

local   replication   postgres                  trust
host    replication   postgres     127.0.0.1/32 trust
host    replication   postgres     ::1/128      trust

Streaming replication

Primary

Benutzer für Replikation anlegen

cr_user_repuser.sh

 #!/bin/sh

 dropuser repuser;
 createuser --login --replication --pwprompt repuser

 name: repuser
 role replication 

Postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 10
max_replication_slots=15
// 1000 x 16 MB damit der standby wal archive hat
wal_keep_segments=1000

pg_hba.conf

host  replication   all   192.168.0.2/32      md5

Replikation Slot anlegen

cr_replication_slot.sql

 SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
 SELECT * FROM pg_replication_slots;      

Daten von Primary zu Secondary kopieren

Script copyFromMaster

    Vorbereitung
    1. pg instanz erzeugen
    2. konfigurationen sichern 
    3. data verzeichnis leeren 
    #!/bin/bash.

     INSTANZ=foo
     DATEI=~/pg_env_${INSTANZ}


    if <span class="createlink"><a href="/wiki/ikiwiki.cgi?do=create&amp;from=postgres&amp;page=_-f___36__DATEI_" rel="nofollow">?</a> -f &#36;DATEI </span>.
       then source $DATEI
       else echo "Kann Datei $DATEI nicht oeffnen => ABBRUCH "
    fi

   set -x
   pg_basebackup --pgdata=/pgdata/${INSTANZ}/data --write-recovery-conf --progress --host=10.61.80.236 \
   --username=repuser

Secondary

 gesicherte Konfiguration zurückkopieren  

Postgresql.conf

 hot_standby = on

Recovery.conf

 standby_mode = 'on'
 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'

 primary_slot_name = 'node_a_slot'

Beide Server stoppen

Secondary starten

Master starten

pg_repack

Migration oracle -> Postgres

https://de.slideshare.net/pgdayasia/migration-from-oracle-to-postgresql