Version du 16 juillet 2012 à 14:00

PG -- PostgreSql

Streaming Replication

Fichiers de config

Le param "config_file" permet de changer le PATH vers le postgresql.conf (i.e. ne pas ne le mettre dans le DATADIR) et donc du streaming.cong (un include) c'est un param à passer dans le ligne de commande du binaire. Donc dans le script d'init.

Il passe très bien dans le -D de l'init et si le data_directory est bien set dans le postgresql.conf

postgresql.conf
streaming.conf  => include dans le postgresql.conf contient les conf spécifiques au streaming
recovery.conf  => les param pour que le slave (standby) sache aller se remonter à partir du master.

faut tester un peut plus tout ça ....

[DOC PG -> http://www.postgresql.org/docs/9.0/static/warm-standby.html#STREAMING-REPLICATION] "Streaming replication is asynchronous, so there is still a small delay between committing a transaction in the primary and for the changes to become visible in the standby. The delay is however much smaller than with file-based log shipping, typically under one second assuming the standby is powerful enough to keep up with the load. With streaming replication, archive_timeout is not required to reduce the data loss window."

WAL

http://www.postgresql.org/docs/9.0/static/wal-configuration.html

Utiles

taille des bases

psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) from pg_database ;"

la taille des index

postgres=# select pg_size_pretty(pg_total_relation_size('tableXX') - pg_relation_size('tableXX'));

#!/bin/bash
for base in `psql -c "select datname from pg_database;" | grep -v "datname" |grep -v \- |grep -v "rows"|grep -v "postgres"`;
do
echo "#############"
echo $base
echo "#############"
for table in `psql $base -c "\d"| grep -v "List of relations" |grep -v + |grep -v rows |grep -v Name |cut -d "|" -f 2`;
do echo $table;
       psql $base -c "select pg_size_pretty(pg_total_relation_size('$table') - pg_relation_size('$table'));";
       echo "+++++++++++++++++";
done 
done

stress

postgres=# CREATE DATABASE damien;
postgres=# \c damien
You are now connected to database "damien" as user "postgres".
damien=# CREATE TABLE damien ( id varchar(55));
ou alors => damien=# CREATE TABLE damien ( id varchar(55)) TABLESPACE table_space_name;
CREATE TABLE
damien=# INSERT INTO damien select generate_series(1,1000000000);