Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Postgresql

  • Les Virtual Private Database (VPD) sur PGSQL

    Les Virtual Private Database (VPD) sur PGSQL sont disponbiles depuis la version 9.1.

     

    source : https://www.enterprisedb.com/blog/virtual-private-database-vpd-ppas-91

  • Show replication status in PostgreSQL

    https://www.niwi.nz/2013/02/16/replication-status-in-postgresql/

     

    select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;

    This query can output can be like this:

    postgres=# select client_addr, state, sent_location,write_location, flush_location, replay_location, sync_priority from pg_stat_replication;

  • Disable Streaming Replication without restarting either master or slave

    I understand the way things work currently, but would it not be more
    convenient to let the replication be stopped and started from SQL commands
    like:

    On Master:

    select pg_pause_streaming_replication(slave_fqdn);
    select pg_resume_streaming_replication(slave_fqdn);

    On Slave:

    select pg_pause_streaming_replication();
    select pg_pause_streaming_replication();

    If Cascaded Streaming Replication is confugured, then

    On Slave:

    select pg_start_streaming_replication(cascaded_slave_fqdn);
    select pg_stop_streaming_replication(cascaded_slave_fqdn);

    Or an OS executable [ either in the core or a contrib module] like

    pg_replication -p primary_fqdn -s slave_fqdn -a pause
    pg_replication -p primary_fqdn -s slave_fqdn -a resume
    pg_replication -p primary_fqdn -s slave_fqdn -a setup

    [ the last one would awesome; and i do not think that it is impossible, and
    would be loved by one and all ]

  • backup

    # Backup script starts here.
    set -x
    #!/bin/bash
    # Location of the backup logfile.
    DEST=/export/BD/dump/postgres
    NAME=dec
    CLU_NAME=DOUX
    HOSTNAME=toto
    # Location to place backups.
    backup_dir="$DEST/$CLU_NAME"
    logfile="$backup_dir/logfile.log"
    touch $logfile
    timeslot=`date +%H-%M`
    databases=`psql -h /var/run/postgresqlcluster$NAME -p 5435 postgres -q -c "l" | sed -n 4,/eof/p | grep -v lignes) | awk {'print $1'} | grep -v template| grep -v "|"`
    TO_C="unix@lulu.fr"
    date > $logfile
    hostname >> $logfile
    for i in $databases; do
        echo "DATABASE $i" >> $logfile
            timeinfo=`date '+%T %x'`
            echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        /usr/bin/vacuumdb -z -h/var/run/postgresqlcluster$NAME -p 5435 -U s-cad-postgresdec $i >/dev/null 2>&1
            /usr/bin/pg_dump $i -h /var/run/postgresqlcluster$NAME -p 5435  | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"
        echo "taille $databases" >> $logfile
        du -hs $backup_dir/postgresql-$i-$timeslot-database.gz >> $logfile
        echo "" >> $logfile
    done
    echo "Bonjour,nn DUMP BASE POSTGRES$NAME sur le serveur $HOSTNAME. nnL'équipe Unix & Linux" | mutt -x -s "DUMP BASE DUMP POSTGRES$NAME" -a ${logfile} -c ${TO_C}

  • Lancement de pgAdmin4

    (pgadmin4) jj771560@utl42642:~/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4/pgadmin4/lib/python2.7$ cd /home/jj771560/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4/
    (pgadmin4) jj771560@utl42642:~/home-global/virtualenv/myVE/lib/python2.7/site-packages/pgadmin4$ python pgAdmin4.py
    pgAdmin 4 - Application Initialisation
    ======================================


    The configuration database - '/home/jj771560/.pgadmin/pgadmin4.db' does not exist.
    Entering initial setup mode...
    NOTE: Configuring authentication for SERVER mode.


    Enter the email address and password to use for the initial pgAdmin user account:

    Email address: josselin_joly@hotmail.com
    Password:
    Retype password: 

    The configuration database has been created at /home/jj771560/.pgadmin/pgadmin4.db
    Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.

     

    Aide : https://stackoverflow.com/questions/41260004/error-trying-to-run-pgadmin4

  • .pgpass

    Utilisation du .pgpass :

    https://newfivefour.com/postgresql-pgpass-password-file.html

     

    attention l'utilisation du .pgpass ne fonctionne pas avec une connexion LDAP :

    psql: FATAL:  authentification LDAP échouée pour l'utilisateur « jj771560 »

     

     

     

  • Localiser ses données en Postgres

    dev=# select oid,datname from pg_database;
      oid  |  datname  
    -------+-----------
     13275 | postgres
         1 | template1
     13274 | template0
     16388 | dev
    (4 lignes)

    dev=# q
    [postgres@utl42644 16388]$ ls -l $PGDATA/base
    total 24
    drwx------ 2 postgres users  4096 25 août  16:28 1
    drwx------ 2 postgres users  4096 25 août  16:28 13274
    drwx------ 2 postgres users  4096  4 oct.  11:01 13275
    drwx------ 2 postgres users 12288  4 oct.  11:00 16388