PostgreSQL

From Alessandro's Wiki
Jump to navigation Jump to search

Command Line Tools

Queries

  • Create an Admin user from command line
CREATE USER user WITH LOGIN PASSWORD 'md530a210c89654b4a3a8255688086158a9' VALID UNTIL '2205-01-01' SUPERUSER ;
  • Get list of databases and their sizes:
SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER BY pg_database_size DESC;

Shell

  • Connect to a PostgreSQL server.
export PGPASSWORD="password"
psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";
  • script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)

<source lang=bash> tables=`$PCOMMAND -c "select tablename from pg_tables;"`

host_src=10.1.1.1 host_dst=10.1.1.2 PCOMMAND="psql -t -A -Upostgres ipaylater_db"

tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"|awk -F"|" '{print $2"."$3}'`

IFS=$'\n' for TABLE in $tables ;do

   unset IFS
   count_src=`$PCOMMAND -h$host_src -c "select count(*) from $TABLE;"`
   count_dst=`$PCOMMAND -h$host_dst -c "select count(*) from $TABLE;"`
   if [ $count_src -ne $count_dst ];then
     echo -e "different record count betwen $host_src ($count_src)\t and $host_dst ($count_dst)\t in table $TABLE"
   fi

done </source>

Configuration

  • some standard locations
    • home:/var/lib/postgres
    • conf:/etc/postgres/x.x/main/
    • logs:/var/log/postgres/x.x/main
  • More Logging

add in postgresql.conf

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'


PostgreSQL GUI's

phpPgAdmin

Quick Fix: (worked with my Version (5.0.3) / pg 9.2.3 )

  1. cd classes/database
  2. copy Postgres84.php to Postgres94.php
  3. open Connection.php
  4. add a line case '9.4': return 'Postgres94'; break; at the // Detect version and choose appropriate database driver switch.
  5. open Postgres.php and copy functions getTablespaces + getTablespace
  6. open Postgres94.php and paste the functions into the class
    1. replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.
    2. change class name to Postgres94

PostgreSQLStudio

http://www.postgresqlstudio.org/download/

TeamPostgreSQL

http://www.teampostgresql.com/