Skip to main content

Posts

Showing posts from April, 2013

psql commands

  \d [NAME]      describe table, index, sequence, or view   \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)                  list tables/indexes/sequences/views/system tables   \da [PATTERN]  list aggregate functions   \db [PATTERN]  list tablespaces (add "+" for more detail)   \dc [PATTERN]  list conversions   \dC            list casts   \dd [PATTERN]  show comment for object   \dD [PATTERN]  list domains   \df [PATTERN]  list functions (add "+" for more detail)   \dg [PATTERN]  list groups   \dn [PATTERN]  list schemas (add "+" for more detail)   \do [NAME]     list operators   \dl            list large objects, same as \lo_list   \dp [PATTERN]...

postgresql dump and restore

sudo -u postgres createuser -D -A -P myuser sudo -u postgres createdb -O myuser mydb pg_dump -i -h  localhost -p 5432 -U myuser -f "/tmp/mydb.sql" mydb --schema=mydb_schema psql -h localhost -p 5432 -U myuser -d mydb < mydb.sql pg_dump -i -h localhost -p 5432 -U myuser -F c -b -v -f "/tmp/mydb.backup" mydb --schema=mydb_schema pg_restore -i -h localhost -p 5432 -U myuser -d mydb -v "/tmp/mydb.backup"

postgresql tricks

Tables are automatically dropped ; select 'drop table "' || tablename || '" cascade;'  from pg_tables where schemaname = 'public'; Sequences are automatically dropped ; SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c where relname like 'myprefix%' Show the current database select current_database(); Show backend server version select version(); Show the current user select current_user; Show the session user select session_user; kill an association SELECT  pg_terminate_backend(procpid) FROM pg_stat_activity   WHERE procpid <> pg_backend_pid();  

PostgreSQL Hot Standby Replication

#Install postgres in the primary and standby server as usual root# wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm #install the repo root# rpm -i pgdg-centos91-9.1-4.noarch.rpm #We now need to edit the CentOS-Base.repo to exclude postgre.Simply edit CentOS-Base.repo and add 'exclude=postgresql*'  to the [base] and [updates] sections: root# cd /etc/yum.repos.d root# vi CentOS-Base.repo     [base]     name=CentOS-$releasever - Base     mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os     #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/     gpgcheck=1     gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6     exclude=postgresql*     #released updates     [updates]     name=CentOS-$releasever - Updates     mirrorlist=http://mirrorlist.cento...