Postgres Cheat Sheet
ADVERTISEMENT
1Installation
START/STOP Postgresql
Download essential rpm's (RHEL):
Start:
postgresql‐8.4.2‐1PGDG.rhel5.x86_64.rpm
#su ‐c 'pg_ctl ‐w start ‐D /var/lib/pgsql/data ‐l /var/lib/pgsql/pgstartup.log' postgres
postgresql‐libs‐8.4.2‐1PGDG.rhel5.x86_64.rpm
on RHEL: # service postgresql start
postgresql‐devel‐8.4.2‐1PGDG.rhel5.x86_64.rpm
$ pg_ctl ‐w start ‐D /var/lib/pgsql/data ‐l /var/lib/pgsql/pgstartup.log
postgresql‐server‐8.4.2‐1PGDG.rhel5.x86_64.rpm
Stop:
postgresql‐docs‐8.4.2‐1PGDG.rhel5.x86_64.rpm
# su ‐c 'pg_ctl ‐w stop ‐D /var/lib/pgsql/data ‐m fast' postgres
Install
on RHEL: # service postgresql stop
rpm ‐ivh postgress*
$ pg_ctl ‐w stop ‐D /var/lib/pgsql/data ‐m smart
=> shutdown normal
Initialize database cluster
$ pg_ctl ‐w stop ‐D /var/lib/pgsql/data ‐m fast
=> shutdown immediate
# mkdir /var/lib/pgsql/data
$ pg_ctl ‐w stop ‐D /var/lib/pgsql/data ‐m immediate
=> shutdown abort
# chown postgres /var/lib/pgsql/data
Status:
# su ‐ postgres
$ pg_ctl status ‐D /var/lib/pgsql/data
$ initdb ‐D /var/lib/pgsql/data
$ ps ‐ef | grep postgres | grep ‐v grep
# service postgresql start
Connecting to database
$ psql
# select * from pg_database;
Local connections as postgres OS user
psql [dbname] [[username]] => sqlplus / as sysdba
Tip: As an alternative to the ‐D option, you can set the
postgres=# \connect template1 postgres => connect postgres@template1
environment variable PGDATA.
Remote connections
psql ‐h 10.0.0.20 ‐p 5432 ‐U myuser ‐d mydatabase => sqlplus myuser@mydatabase
Backup & Recovery
psql ‐h 10.0.0.20 ‐p 5432 ‐l => list all databases listening on the host/port, may need
to add entry to pg_hba.conf/pg_ident.conf
3 types of backup approaches
Default DB listen port ‐ 5432
* SQL dump
* File system level backup
Important file locations
* Continuous archiving
Configuration files
$PGDATA ‐ on RHEL /var/lib/pgsql/data ‐ location of the database
Dumps (exp/imp)
$PGDATA/postgresql.conf => init.ora
Backup:
$PGDATA/pg_hba.conf => (sort of sqlnet.ora)
pg_dump mydatabase > mydump.out => exp
$PGDATA/pg_ident.conf => OS/DB user mapping file
pg_dumpall > myfulldump.out => exp full=Y
$PGDATA/pg_xlog => log_archive_dest
Server Logfiles
Restore:
$PGDATA/pg_log/postgresql‐%a.log
pg_restore ‐d mydatabase mydump.out
/var/lib/pgsql/pgstartup.log
psql mydatabase < mydump.out
Directories
psql ‐f myfulldump.out postgres
Executables ‐ /usr/bin
Libraries ‐ /usr/lib
Copy database to another node:
Documentation ‐ /usr/share/doc/postgresqlx.y.z
pg_dump ‐h host1 mydatabase | psql ‐h host2 mydatabase
Data ‐ /var/lib/pgsql/data
Backup area ‐ /var/lib/pgsql/backup
Filesystem backup:
Templates ‐ /usr/share/pgsql
tar ‐cf backup.tar /var/lib/pgsql/data
Procedural Languages ‐ /usr/lib/pgsql
! database server have to shutdown cleanly!
Development Headers ‐ /usr/include/pgsql
Other shared data ‐ /usr/share/pgsql
Taking advantage of WAL logging:
‐ WAL Logs stored by default in $PGDATA/pg_xlog
Important Init parameters
Performance
‐ database can be restored by "replaying" transaction logs
File locations
pg_stat_% => v$views
‐ hot standby concept can be achieved with WAL logs
show config_file; => show parameters spfile;
Get explain plan for query
‐ do not try to combine WAL logs with pg_dump files for
show all; => show parameters;
EXPLAIN ANALYZE SELECT *
restoring in another location, FS dump is needed to be able to
show data_directory; => show db file dir location
FROM MY_TABLE;
recover WAL log
show hba_file;
Update stats on table
show ident_file;
ANALYZE MY_TABLE;
Standalone hot backup:
show log_directory;
Vacuum table
Ensure parameters are defined and directories exist:
show log_destination;
VACUUM MY_TABLE;
archive_mode=on
show external_pid_file;
Vacuum & Analyze whole DB
archive_command = 'test ! ‐f
Memory/resources
VACUUM ANALYZE;
/var/lib/pgsql/backup_in_progress || cp ‐i %p
show effective_cache_size;
Analyze – update stats
/var/lib/pgsql/archive/%f < /dev/null'
show shared_buffers;
Vacuum – housekeep after
show vacuum_mem;
heavy insert/delete ops
touch /var/lib/pgsql/backup_in_progress
show wal_buffers;
psql ‐c "select pg_start_backup('hot_backup');"
Server settings
tar ‐cf /var/lib/pgsql/backups/backup.tar /var/lib/pgsql/data/
show listen_addresses;
psql ‐c "select pg_stop_backup();"
show port;
rm /var/lib/pgsql/backup_in_progress
show server_encoding;
tar ‐rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
Version 1.0 –RedHat OS specific
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education