PostgreSQL

From GeilThings

Jump to: navigation, search
PostgreSQL
Elephant.png
General
Version 9.2.4
Stable 9.2.4
Function Database
SQL SiSQL
Port 5432
Files
Config File /var/lib/pgsql/9.2/data/postgresql.conf
Data File /var/lib/pgsql/9.2/data
Log File /home/postgres/logfile
Scripts
Start su postgres -c "/usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile"
Stop su postgres -c "/usr/pgsql-9.2/bin/pg_ctl stop -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile"
Cli psql

Contents

    Elefante jm klein.jpg



General

  • Getting Started - http://lca2007.linux.org.au/att_data/Miniconfs(2f)PostgreSQL/attachments/getting_started.pdf
  • Determine PostgreSQL version: as postgres user, run "psql databasename". The version appears in the welcome message; it can also be asked via SELECT version(). psql --version shows the version of the psql client (which here is at the same time the version of the database server).
  • Run start and stop scripts as the postgres user.
    • Start: /usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile
    • Stop: /usr/pgsql-9.2/bin/pg_ctl stop -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile
      • If server hangs while shutting down: (waiting for server to shut down... failed pg_ctl: server does not shut down) force with -m option, see http://bit.ly/WU2TOX:
      • /usr/pgsql-9.2/bin/pg_ctl stop -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile -m fast
  • Logos, icons: http://www.postgresql.org/community/propaganda
  • PostgreSQL Magazine: This is the only Magazine in all Databases / Programming Languages I know of. Very nice looking, very interesting themes. Great! http://pgmag.org/01/start

Installation

Using source, 9.0.4

# The older 8.1 and 8.4 (PostgreSQL84) can be found in yum
# Going for the newer 9.0.x
# Following
# http://davidghedini.blogspot.com/2010/10/install-postgresql-9-on-centos.html
# 9.0.4 cannot be found there
#
# Also: going for source
# Following http://www.postgresql.org/docs/9.0/interactive/install-short.html
wget http://wwwmaster.postgresql.org/redir/56/h/source/v9.0.4/postgresql-9.0.4.tar.gz
tar -zvxf postgresql-9.0.4.tar.gz
cd postgresql-9.0.4
gmake
gmake install
adduser postgres
passwd postgres 
> mypassword
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
#
# From http://www.postgresql.org/docs/9.0/interactive/install-procedure.html
# Free disk space by removing the built files from the source tree and  
# reset the source tree to the state in which it was distributed
gmake distclean
#
# From http://www.postgresql.org/docs/9.0/interactive/install-procedure.html
# Enable the run-time linker to find the shared libraries faster
/sbin/ldconfig /usr/local/pgsql/lib 
#
#add to /etc/profile
PATH=$PATH:/usr/local/pgsql/bin
export PATH
#add it also to .bash_profile from current user
#
su - postgres
#
# Initialize postgresql
# TODO - All local users are able to connect to the database. This is M!3&)4
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
#
# Start
postgres -D /usr/local/pgsql/data >logfile 2>&1 &
# Another form: pg_ctl start -D /usr/local/pgsql/data -l /home/postgres/logfile
#
# In ps aux some processes can be seen now:
#
# postgres ... postgres -D /usr/local/pgsql/data
# postgres ... postgres: writer process
# postgres ... postgres: wal writer process
# postgres ... postgres: autovacuum launcher process
# postgres ... postgres: stats collector process
#
# A much better option would probably be to use the official postgresql yum repository:
# http://yum.pgrpms.org/

Using yum, 9.1.0, 9.1.1, 9.1.2, 9.1.3, 9.1.4, 9.2

# Set python to the default python.
PYTHONHOME=/usr
 
# http://yum.pgsqlrpms.org/howtoyum.php
# I want to use only the "official" yum repository from PostgreSQL
su - root
cd /etc/yum.repos.d
# On the enabled sections for relevant repositories (e.g. CentOS-Base.repo, [base] and [updates] sections) add
# exclude=postgresql*
 
cd /opt
# The rpms have non-corresponding version numberings to the official version, 
# unexpected for a respected database like postgresql.
# 9.1.1: wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm
# 9.1.4: wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos-9.1-3.noarch.rpm
wget http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-5.noarch.rpm
 
# Install the rpm. 
# ------- This only needs to be done once for every 9.x version. -------
rpm -ivh pgdg-centos92-9.2-5.noarch.rpm
# In /etc/yum/repos.d, a new repository file will be created, e.g. pgdg-91-centos.repo
 
# To see which postgresql versions I can install from yum:
yum list | grep postgresql
 
# Check that the new version is there:
# 9.1.x: yum info postgresql91
yum info postgresql92
 
# Use yum install the first time, yum update for upgrades.
yum update postgresql91 postgresql91-devel postgresql91-server postgresql91-libs 
yum update postgresql91-contrib postgresql91-plperl
yum update postgresql91-plpython postgresql91-python
# postgresql91-jdbc: Stoppped installing it in CentOS 6.2 because of java-1.5.0-gcj dependency, which may break the current java.
 
# PostgreSQL9.1 creates a data directory: /var/lib/pgsql/9.1/data if it does not exist.
# The installation through yum installs postgresql9.1 in /usr/pgsql-9.1. Blöd.
 
# Initialize postgresql
su - postgres
 
# To avoid a possible error message while using initdb:
#   initdb: directory "/var/lib/pgsql/9.1/data" exists but is not empty
#   If you want to create a new database system, either remove or empty
#   the directory "/var/lib/pgsql/9.1/data" 
# Move the data directory first
mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data_old
 
/usr/pgsql-9.1/bin/initdb -D /var/lib/pgsql/9.1/data
# TODO: WARNING: enabling "trust" authentication for local connections
# You can change this by editing pg_hba.conf or using the -A option the
# next time you run initdb.
 
# If relevant, set startup level after reboot.
# I don't use this.
# chkconfig --level 345 postgresql-9.1 on

Running PostgreSQL

  • TODO - All local users are able to connect to the database. This is M!3&)4
  • Create a home directory for the postgres user.
usermod -d /home/postgres postgres
mkdir /home/postgres
chown -R postgres. /home/postgres/
  • Start the Server with:
/usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile
  • Cli.
psql
  • Connect with the postgresql console the "databasename" database.
psql databasename
  • Exit the cli.
\q
  • Create database.
create database databasename;
  • Remove database.
drop database databasename;
  • Create database user.
create user username;
  • Remove user.
drop user username;
  • List databases, owner, etc.
\l
  • Other:
CREATE TABLE HelloWorld (pgsqlkey VARCHAR (50) NOT NULL, language VARCHAR (50), message_text VARCHAR (255), msgDate TIMESTAMP, PRIMARY KEY (pgsqlkey));
 
INSERT INTO HelloWorld VALUES ('java', 'Java', 'Hello World from Java, Tomcat, and PostgreSQL', '2011-07-23 19:59:00');
INSERT INTO HelloWorld VALUES ('node.js', 'Node.js', 'Hello World from Node.js and PostgreSQL', '2011-07-23 20:00:00');
INSERT INTO HelloWorld VALUES ('php', 'PHP', 'Hello World from PHP and PostgreSQL', '2011-07-23 20:00:00');
INSERT INTO HelloWorld VALUES ('perl', 'Perl', 'Hello World from Perl and PostgreSQL', '2011-07-23 20:00:00');
INSERT INTO HelloWorld VALUES ('scala', 'Scala', 'Hello World from Scala, Tomcat, and PostgreSQL', '2011-07-23 20:00:00');
INSERT INTO HelloWorld VALUES ('python', 'Python', 'Hello World from Python and PostgreSQL', '2011-09-11 20:55:00');
INSERT INTO HelloWorld VALUES ('ruby', 'Ruby', 'Hello World from Ruby and PostgreSQL', '2011-09-11 20:56:00');
 
SELECT * FROM HelloWorld;
 
UPDATE HelloWorld SET message_text = 'Hello World from Python and PostgreSQL' WHERE language = 'Python';

Backup PostgreSQL

Some reading about PostgreSQL backups: http://bit.ly/12t3NWu

######### START OF EDIT #########
 
APPNAME=postgresql
# The directory where I want to download the files to be installed.
DOWNLOADDIRECTORY=/opt
# If I want to backup, set it to TRUE.
ISBACKUP=TRUE
# BACKUPROOTDIR is only relevant if ISBACKUP=TRUE
BACKUPROOTDIR=/home/backup
DATE=`date +%Y-%m-%d`
 
RAWVERSION=`psql --version`
OLDVERSION=${RAWVERSION:18:5}
echo $OLDVERSION
 
# The script breaks if variable names are not enclosed with {}
BACKUPDIR=${BACKUPROOTDIR}/${APPNAME}/${APPNAME}_${OLDVERSION}_${DATE}
 
######### END OF EDIT #########
 
if [[ ${ISBACKUP} == "TRUE" ]]; then
  # Create backup directory if it does not exist.
  if [ ! -d "${BACKUPDIR}" ]; then
    mkdir -m 755 -p ${BACKUPDIR}
  fi
  # Backup data.
  cd ${BACKUPDIR}
  # Only one backup (sql or data files) would be actually needed for an upgrade.
  # sql backup. PostgreSQL must be running so pg_dumpall  can run.
  pg_dumpall -U postgres -f postgresqldb.sql
  # Data files backup.
  if [ ! -d "${BACKUPDIR}/data" ]; then
    mkdir -m 755 -p ${BACKUPDIR}/data
  fi
  # This with the 9.x in the path is not good. TODO: Change this to another path.
  cp -pr /var/lib/pgsql/9.2/data/* $BACKUPDIR/data/
fi

Upgrade PostgreSQL

pg_upgrade, a useful command to upgrade PostgreSQL: http://bit.ly/12t35bI

######### START OF EDIT #########
 
APPNAME=postgresql
# The directory where I want to download the files to be installed.
DOWNLOADDIRECTORY=/opt
# If I want to backup, set it to TRUE.
ISBACKUP=TRUE
# BACKUPROOTDIR is only relevant if ISBACKUP=TRUE
BACKUPROOTDIR=/home/backup
DATE=`date +%Y-%m-%d`
 
RAWVERSION=`psql --version`
OLDVERSION=${RAWVERSION:18:5}
echo $OLDVERSION
 
# The script breaks if variable names are not enclosed with {}
BACKUPDIR=${BACKUPROOTDIR}/${APPNAME}/${APPNAME}_${OLDVERSION}_${DATE}
 
######### END OF EDIT #########
 
# Backup.
if [[ ${ISBACKUP} == "TRUE" ]]; then
  # Create backup directory if it does not exist.
  if [ ! -d "${BACKUPDIR}" ]; then
    mkdir -m 755 -p ${BACKUPDIR}
  fi
  # Backup data.
  cd ${BACKUPDIR}
  # Only one backup (sql or data files) would be actually needed for an upgrade.
  # sql backup. PostgreSQL must be running so pg_dumpall  can run.
  pg_dumpall -U postgres -f postgresqldb.sql
  # Data files backup.
  if [ ! -d "${BACKUPDIR}/data" ]; then
    mkdir -m 755 -p ${BACKUPDIR}/data
  fi
  # This with the 9.x in the path is not good. TODO: Change this to another path.
  cp -pr /var/lib/pgsql/9.2/data/* $BACKUPDIR/data/
fi
 
# Shutdown applications using PostgreSQL. 
# ...
 
# Shutdown the server.
su - postgres
# 9.0.4: pg_ctl stop -D /usr/local/pgsql/data -l logfile
/usr/pgsql-9.2/bin/pg_ctl stop -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile
 
su - root
## password
# Move the old server directory.
# 9.0.4: mv /usr/local/pgsql /usr/local/pgsql.old
mv /usr/pgsql-9.2 /usr/pgsql-9.2.old
 
# Install (using yum)
# yum update for upgrading to a new minor version (e.g. 9.1.4 to 9.1.5), 
# yum install for upgrading to a new major version, e.g. 9.1.5 to 9.2.0
yum update postgresql92 postgresql92-devel postgresql92-server postgresql92-libs 
yum update postgresql92-contrib postgresql92-plperl
yum update postgresql92-plpython pgdg-centos92
su - postgres
mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data_old/
/usr/pgsql-9.2/bin/initdb -D /var/lib/pgsql/9.2/data
 
# Both servers are not running, the new server was only initialized (initdb).
# Needs write access to the directory where pg_upgrade is run.
 
# Use argument -c for check only first.
/usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.2.old/bin -B /usr/pgsql-9.2/bin -d /var/lib/pgsql/9.2/data_old -D /var/lib/pgsql/9.2/data -c
# Message: Clusters are compatible
 
/usr/pgsql-9.2/bin/pg_upgrade -b /usr/pgsql-9.2.old/bin -B /usr/pgsql-9.2/bin -d /var/lib/pgsql/9.2/data_old -D /var/lib/pgsql/9.2/data
# Possible Error: There seems to be a postmaster servicing the old cluster
# Please shutdown that postmaster and try again.
# Failure, exiting
# Solution: Check running process (ps aux | grep pgsql), kill it.
 
# Message: Upgrade complete
# ... Running this script will delete the old cluster's data files:
#       /home/postgres/delete_old_cluster.sh
#
# Start the server as postgres user.
/usr/pgsql-9.2/bin/pg_ctl start -D /var/lib/pgsql/9.2/data -l /home/postgres/logfile
 
# With ps aux, check that PostgreSQL processes are running:
ps aux | grep postgres
# postgres  ... /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.1/data
# postgres  ... postgres: logger process
# (This checkpointer appeared after 9.2)
# postgres  ... postgres: checkpointer process 
# postgres  ... postgres: writer process
# postgres  ... postgres: wal writer process
# postgres  ... postgres: autovacuum launcher process
# postgres  ... postgres: stats collector process
 
# Check version.
psql --version
# Or
psql databasename
## Message:
## psql (9.2.4)
## Type "help" for help.
# Leave with \q.
 
# If applicable,
# if upgrading to a major version, change the value of the variable PG_CONFIG and the path tp PostgreSQL in the PATH variable.
 
# PG_CONFIG=/usr/pgsql-9.1/bin/pg_config
# PG_CONFIG=/usr/pgsql-9.2/bin/pg_config
 
# /usr/pgsql-9.1/bin
# /usr/pgsql-9.2/bin
 
# Either restart the ssh session or reload the /etc/profile file.
source /etc/profile
 
# If applicable, deactivate the old version PostgreSQL Repo.
# In /etc/yum.repos.d/pgdg-91-centos.repo, set enabled=0
 
# Test applications.
 
# If ok, delete everything old, but not the backups.
/home/postgres/delete_old_cluster.sh
 
su - root
rm -rf /usr/pgsql-9.2.old

Errors

libpq.so.5 cannot open

  • Starting PostgreSQL the message pops:

/usr/pgsql-9.2/bin/pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory.

  • Solution: Add the PostgreSQL libraries (e.g. /usr/pgsql-9.2/lib) to LD_LIBRARY_PATH in /etc/profile and export it.

Programming PostgreSQL


Cloud

Versions

Software name Version number Version date
PostgreSQL 9.2.2
9.1.0
9.2.3
9.2.4
8 December 2012
15 October 2011
8 February 2013
6 April 2013

Comments

blog comments powered by Disqus