User Tools

Site Tools


middleware:postgresql

PostgreSQL

Configuration

Enable XA

Parameters for using XA, in testing environment. In postgresql.conf:

listen_addresses = '*'
max_connections = 100
shared_buffers = 512MB
effective_cache_size = 1024MB
max_prepared_transactions = 100

Data management

Upgrade Postgres on RHEL

Example: migrate from 9.2 to 9.3:

  • Install new version of Postgres
  • Init the new DB: /etc/init.d/postgresql-9.3 initdb
  • Customize pg_hba.conf and postgresql.conf
  • Stop old DB: service postgresql-9.2 stop
  • Start new DB: service postgresql-9.3 start
  • Run migration script:
/usr/pgsql-9.3/bin/pg_upgrade -v \
  -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ \
  -d /var/lib/pgsql/9.2/data/ -D /var/lib/pgsql/9.3/data/
  • Runstat the new db: ./analyze_new_cluster.sh
  • Test and remove old data: ./delete_old_cluster.sh

Administration

Change ownership of all objects

  REASSIGN OWNED BY old_role TO new_role

Delete all objects in a DB

If all objects are in public:

DROP SCHEMA public CASCADE;

CREATE SCHEMA public AUTHORIZATION postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'standard public schema';

Run a SQL script

export PGHOST=psql.example.com
export PGDATABASE=mydatabase
export PGUSER=myuser
export PGPASSWORD=itsasecret
SCRIPT=ilmioscript.sql
psql -v ON_ERROR_STOP=Yes -1 -w -a -f ${SCRIPT} $PGDATABASE > ${SCRIPT}.log 2>&1 && echo OK || echo ERROR
middleware/postgresql.txt · Last modified: 2017/01/05 16:43 by ap

Informativa sui cookie