Difference between revisions of "Postgres SQL"

From Blue-IT.org Wiki

(Upgrade - dump and restore)
 
(100 intermediate revisions by 2 users not shown)
Line 2: Line 2:
 
This guide is tested on ubuntu feisty with postgresql 8.2. It should work for former and future versions of both too.
 
This guide is tested on ubuntu feisty with postgresql 8.2. It should work for former and future versions of both too.
  
General documentation you will find here: [http://www.postgresql.org/docs/8.2/interactive/ postgresql.org - interactive (!) manual with comments].
+
General documentation you will find here:  
 +
* [http://www.postgresql.org/docs/8.2/interactive/ postgresql.org - interactive (!) manual with comments]
 +
* [http://sql-info.de/postgresql/links.html SQL Info Links]
 +
* (GERMAN) [http://www.selflinux.org/selflinux/html/postgresql.html Postgres on Selflinux.org]
 +
* (GERMAN) [http://www.postgres.de/index.whtml German Postgres Page]
  
 
You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles'').
 
You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles'').
  
===User Management===
+
=== User Management ===
 +
See the excellent online documentation:
 
* [http://www.postgresql.org/docs/8.2/interactive/user-manag.html postgresql-org - user management].
 
* [http://www.postgresql.org/docs/8.2/interactive/user-manag.html postgresql-org - user management].
  
===General Dokumentation===
+
=== Common Tasks ===
* (GERMAN) [http://www.selflinux.org/selflinux/html/postgresql.html Postgres on Selflinux.org]
+
==== mysql: CREATE DATABASE ====
* (GERMAN) [http://www.postgres.de/index.whtml German Postgres Page]
+
postgresql: CREATE DATABASE db_name OWNER username;
 +
postgresql:
 +
 
 +
==== mysql: SHOW DATABASES ====
 +
postgresql: \l
 +
postgresql: SELECT datname FROM pg_database;
 +
 
 +
==== mysql: USE DATABASE ====
 +
postgresql: \c databsename
 +
 
 +
==== mysql: SHOW TABLES ====
 +
postgresql: \d
 +
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
 +
 
 +
==== mysql: SHOW COLUMNS ====
 +
postgresql: \d table
 +
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';
 +
 
 +
* Read more: http://www.linuxscrew.com/2009/07/03/postgresql-show-tables-show-databases-show-columns/#ixzz1DYiUdglU
 +
 
 +
==== Copy Database ====
 +
su postgres
 +
psql
 +
 
 +
postgres=# \l 
 +
                                          List of databases 
 +
    Name    |    Owner    | Encoding |  Collation  |    Ctype    |  Access privileges   
 +
-------------+---------------+----------+-------------+-------------+----------------------- 
 +
mydb        | name_of_owner | UTF8    | en_US.UTF-8 | en_US.UTF-8 | 
 +
 
 +
postgres=# CREATE DATABASE newdb WITH TEMPLATE mydb OWNER name_of_owner;
 +
 
 +
* See: http://stackoverflow.com/questions/876522/creating-a-copy-of-a-database-in-postgresql
 +
 
 +
= Ubuntu 12.04 and above =
 +
Alle major versions of postgresql are supported directly from the following site:
 +
* http://wiki.postgresql.org/wiki/Apt
 +
 
 +
Create and enable a new sources.list file
 +
sudo vim /etc/apt/sources.list.d/pgdg.list
 +
 
 +
deb http://apt.postgresql.org/pub/repos/apt/VERSION-pgdg main
 +
 
 +
with VERSION is presice, trusty, ....
 +
 
 +
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 +
sudo apt-get update
 +
sudo apt-get install postgresql-8.4
 +
 
  
==Ubuntu Feisty, Gutsy==
+
=Ubuntu Feisty, Gutsy, Hardy, ..., Maverick (8.4) =
 
* Thanks to the article on [http://hocuspok.us/journal/postgresql-on-ubuntu-linux-how-to-updated hocuspok.us], it was very easy to set up postgresql-8.2 running on ubuntu.
 
* Thanks to the article on [http://hocuspok.us/journal/postgresql-on-ubuntu-linux-how-to-updated hocuspok.us], it was very easy to set up postgresql-8.2 running on ubuntu.
 
* Another Quicklink for ubuntu: [https://help.ubuntu.com/community/PostgreSQL PostgreSQL Quickstart]
 
* Another Quicklink for ubuntu: [https://help.ubuntu.com/community/PostgreSQL PostgreSQL Quickstart]
 +
* And look at the official [https://help.ubuntu.com/8.04/serverguide/C/postgresql.html ubuntu server documentation (hardy)]
 +
 +
==Backup==
 +
Also see next section "Upgrade".
 +
 +
From [http://www.cyberciti.biz/tips/backup-postgres-mysql-securely-using-ssh.html here] I have the following idea:
 +
 +
Dump Postgres Database using ssh. Use pg_dump command command:
 +
pg_dump -U USERNAME YOUR-DATABASE-NAME | \
 +
ssh user@remote.server.com "dd of=/pgsql/$(date +'%d-%m-%y')"
 +
 +
==Upgrade - dump and restore==
 +
Generally look at
 +
* [http://www.postgresql.org/docs/8.3/static/install-upgrading.html postgres doc - upgrading (8.3)]
 +
* [http://www.postgresql.org/docs/8.1/static/backup.html postgres doc - backup and restore (8.1)]
 +
 +
Upgrading from major versions (e.g. 8.1 to 8.3) is done via an database dump/restore cycle:
 +
 +
Dump...
 +
su postgres
 +
pg_dumpall > /path/to/all_pgsql_db_dump
 +
pg_dumpall | gzip -c > /path/to/all_pgsql_db_dump.gz
 +
 +
and restore a single database
 +
sudo -u postgres psql database_to_restore  < all_pgsql_db_dump
 +
 +
or everything:
 +
sudo -u postgres psql < /path/to/all_pgsql_db_dump
 +
 +
 +
According to [http://ogasawalrus.com/blog/node/462#comment-10862 this blog entry] the following is easy using the cluster upgrade method:pg_upg
 +
 +
"''On debian / ubuntu, it's easy to do and there's a command pg_upgradecluster that takes as argument the versions and the locations of the two clusters (a pg installation is called a cluster) and then initdbs the new one like the old one, and initiates a pg_dumpall from the old to the new and voila, you're migrated. Really, it's pretty seamless.''"
 +
 +
==Installation==
 +
Install postgresql (version 8.3 for ubuntu hardy / 8.04)
 +
VER=8.3; sudo apt-get install postgresql-$VER postgresql-client-$VER
 +
 +
If postgesql won't start due to locale settings please do either:
 +
 +
* edit your SERVER '''sshd_config''' file
 +
> vim /etc/ssh/sshd_config
 +
# Allow client to pass locale environment variables
 +
# AcceptEnv LANG LC_*
 +
 +
* edit  your CLIENT '''ssh_config''' file:
 +
> vim /etc/ssh/sshd_config
 +
#    SendEnv LANG LC_*
 +
 +
* add this at the end of your '''/etc/profile'''
 +
> vim /etc/profile
 +
[...]
 +
export LANGUAGE="en"
 +
export LANG="C"
 +
export LC_MESSAGES="C"
 +
 +
=== Postgres, Mysql, php5, apache2 ===
 +
Don't forget to install the appropriate dbo's:
 +
apt-get install php5-pgsql php5-mysql
  
===Installation===
+
=== Pgadmin3 ===
Install postgresql
+
Do not use on server environment in the internet, and open the ports to outside access!!!
sudo apt-get install postgresql-8.2 postgresql-client-8.2
+
Using VPN and a firewall is no problem.
  
 
For administration we install pgadmin3
 
For administration we install pgadmin3
Line 26: Line 138:
 
===Set database admin account===
 
===Set database admin account===
 
Alter the password for the standard ''postgres'' user account (as root)
 
Alter the password for the standard ''postgres'' user account (as root)
  sudo su
+
  sudo su postgres -c psql template1
postgres -c psql template1
+
 
 
Inside the database:
 
Inside the database:
 
  template1=# ALTER USER postgres WITH PASSWORD 'new_password';
 
  template1=# ALTER USER postgres WITH PASSWORD 'new_password';
 
  template1=# \q
 
  template1=# \q
  
===''/etc/postgresql/8.2/main/postgresql.conf''===
+
===Alter the unix user postgres===
Assure that the server is accessible on localhost
+
sudo passwd -d postgres
  sudo gedit /etc/postgresql/8.2/main/postgresql.conf
+
  sudo su postgres -c passwd
  
  listen_addresses = 'localhost'
+
===Enable the admin pack for use with pgadmin3===
 +
VER="8.3"; sudo apt-get install postgresql-contrib-$VER
 +
sudo su postgres -c psql < /usr/share/postgresql/${VER}/contrib/adminpack.sql
 +
 
 +
=== Ubuntu Configuration files ===
 +
'''/etc/postgresql/8.2/main/postgresql.conf'''
 +
 
 +
Assure that the server is accessible on localhost and from the servers ip.
 +
VER="8.3"; sudo gedit /etc/postgresql/${VER}/main/postgresql.conf
 +
 
 +
# - Connection Settings -
 +
# what IP address(es) to listen on;
 +
# comma-separated list of addresses;
 +
  listen_addresses = 'localhost,server_ip'
 
  password_encryption = on
 
  password_encryption = on
  
===''/etc/postgresql/8.2/main/pg_hba.conf''===
+
'''/etc/postgresql/VERSION/main/pg_hba.conf'''
 +
 
 
Security and server settings. If you like to use the server in a network you have to pay attention to three points:
 
Security and server settings. If you like to use the server in a network you have to pay attention to three points:
 +
 
# Add the server ip to the ''listen'' adress
 
# Add the server ip to the ''listen'' adress
 
# Allow special clients to connect
 
# Allow special clients to connect
 
# Think about using ssl, if you connect through the internet
 
# Think about using ssl, if you connect through the internet
  
  sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
+
  VER="8.3"; sudo gedit /etc/postgresql/${VER}/main/pg_hba.conf
 +
VER="8.4"; sudo gedit /etc/postgresql/${VER}/main/pg_hba.conf
 +
 
 +
For Maverick (8.4) the following (and only those are necessary):
 +
 +
# Connections for all PCs on the subnet
 +
#
 +
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
 +
# e.g. host all all 192.168.0.0/24 md5
 +
# e.g. host all all 192.168.0.10 255.255.255.0 md5
 +
host all all [ip address] [subnet mask] md5
 +
 
 +
For all other Ubuntu Versions additionally alter the following entries:
  
# - Connection Settings -
 
# what IP address(es) to listen on;
 
# comma-separated list of addresses;
 
listen_addresses = 'localhost,server_IP'
 
 
 
 
  # Allow any user on the local system to connect to any database under
 
  # Allow any user on the local system to connect to any database under
 
  # any user name using Unix-domain sockets (the default for local
 
  # any user name using Unix-domain sockets (the default for local
Line 68: Line 202:
 
  # IPv6 local connections:
 
  # IPv6 local connections:
 
  host all all ::1/128 md5
 
  host all all ::1/128 md5
 
# Connections for all PCs on the subnet
 
#
 
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
 
# e.g. host all all 192.168.0.0/24 md5
 
# e.g. host all all 192.168.0.10 255.255.255.0 md5
 
host all all [ip address] [subnet mask] md5
 
  
 
===Restart postgresql===
 
===Restart postgresql===
  sudo /etc/init.d/postgresql-8.2 restart
+
  VER="8.3"; sudo /etc/init.d/postgresql-$VER restart
  
=SuSE 10.1, 10.3=
+
Maverick (8.4):
==Installation==
+
VER="8.4"; sudo /etc/init.d/postgresql restart
Via YaST.
+
 
 +
=openSuSE 10.1, 10.3=
 +
''SuSE oh SuSE ... why not just simply as debian, redhat & Co. ... !?''
 +
 
 +
First I have to mention that it is almost impossible to get a ''howto'' on ''postgresql'' configuration on openSuSE. But what made me really angry was, that the sample files delivered with ''opensuse'' are completely useless to get a running system. E.g. the localisation settings of the server do not default to unicode.
 +
 
 +
Not even a search at ''support database of SuSE'' or ''Novell's support database'' gave me ANY useful result. This is really ''poor''. For an example see [http://www.justlinux.com/forum/showthread.php?t=150426 this thread]. Finding an according ''howto'' for ubuntu and getting startet with the server and pgadmin was a matter of minutes.
 +
 
 +
Second it is not good to change the location and content (!) of configuration files from version to version (10.1 -> 10.3) as this is the case! SuSE should follow the example of debian or redhat. A database system is not a playground for such exeriments and really NOT necessary.
 +
 
 +
Third without any comment: why does postgres on SuSE 10.3 not use utf-8 by default any more as it did on 10.1 !?
 +
 
 +
One could draw the conclusion, that ''NOVELL(former SuSE)-users(!)'' have not much experience with ''postgresql'' databases, compared with ''debian''. This is not very appeasing if you consider using this in a sensible environment with the default configuration.
 +
 
 +
I am sorry to say, but setting up ''postgresql'' on a ''debian/ubuntu'' system that supported ''postgres'' from its beginning might be a better choice not just for inexperienced users.
 +
 
 +
==Installation of ''postgresql-server''==
 +
This is easily done via yast or zypper
 +
zypper install postgresql-server
  
 
==Administration==
 
==Administration==
* via pgadminIII. You will find them in the guru repositories:
+
===Pgadmin III===
http://ftp.gwdg.de/pub/linux/misc/suser-guru/rpm/10.1/RPMS
+
Pgadmin is good for managing the database server '''after''' it is configured and running. The editing of the configuration files should be made by hand - doiing it with pgadmin is a little bit complicated.
  
* all configuration files are in
+
* Install prior to opensuse 10.3
/usr/share/postgresql
+
** via YaST and the ''guru'' repository
 +
* opensuse 10.3 repositories and later do not contain ''pgadmin'' any more
 +
**Download the rpm package from the pgadmin site: [http://www.pgadmin.org/download/opensuse.php Direct from pgadmin.org] or [http://www.postgresql.org/ftp/pgadmin3/release/v1.8.0/opensuse/10.3/ pgadmin 10.3] and install them with a right mouse klick on the ''rpm'' file.
  
You have to copy the ''/usr/share/postgresql/*.sample'' files to ''.conf'' and edit them according to your needs.
+
===Lack of administration module===
 +
Later, when you log into the database, '''pgadmin''' and try to click on the '''postgres''' system database, ''pgadmin'' complains about lacking the '''adminpack contrib module'''.
  
=Administration with Pgadmin III=
+
There is a package ''postgresql-contrib'' that includes additional modules for the server. But the postgres modules have to be configured separately - according to the postgresql documentation.
Pgadmin helps you with almost all administration tasks to manage your postgresql databases. From managing user accounts (roles), creating tables, schemes, constraints (e.g. primary keys) to an extended sql editor.
 
  
===Initial registration with pgAdminIII===
+
This is no problem, when working with other databases than ''postgres''!
To be able to manage your database with pgadmin you have to configure it for the specific server:
 
  
[[Image:PdAdminIII_initial_registration.png|600px|Working with pgAdminIII ]]
+
===Post Installation===
 +
Start the server for the first time:
 +
rcpostgresql start
  
===Queries with pgadminIII===
+
Login as user ''postgres'' and alter the table. Instead of using the ''template1'' database like in ''ubuntu/debian''
A very good tool for working with the database is the integrated '''sql query editor''' of ''pgadmin''. Select the database you want to work with, then you can choose the query tool from the menu.
+
su postgres -c psql postgres
 +
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
 +
ALTER ROLE
 +
postgres=# \q
  
PostgreSQL queries are ''case sensitive''. So if you specified '''U'''ppercase names for ''columns'' you have to use them '''exactly''' and in ''quotes''. It might be necessary to add the ''schemata'' name postfixed by a '''dot''' to get a  proper query:
+
==openSuSE configuration files==
  select "Prename" from myscheme."Client";
+
Confusing:
 +
* all '''configuration''' files are owned by '''postgres:postgres''' and placed in
 +
/var/lib/pgsql/share
 +
* all '''sample''' configuration files are owned by '''root:root''' and placed in
 +
  /usr/share/postgresql
  
Press <F5> to start the query.
+
Copy the ''/usr/share/postgresql/*.conf.sample'' files to ''/var/lib/pgsql/share/*.conf'' and alter their permissions/owner:
 +
(SuSE 10.3) cd /var/lib/pgsql/share
 +
(SuSE 10.1) cd /var/lib/pgsql/data
  
You can easily save the queries in a file for later reuse.
+
The next is for 10.3 only:
 +
(suSE 10.3 only !)
 +
cp /usr/share/postgresql/pg_hba.conf.sample pg_hba.conf
 +
cp /usr/share/postgresql/postgresql.conf.sample postgresql.conf
 +
chown postgres:postgres pg_hba.conf postgresql.conf
 +
chmod 600 postgresql.conf pg_hba.conf postgresql.conf
  
===Backup and restore  with pgadminIII===
+
'''/var/lib/pgsql/share/pg_hba.conf'''
PgadminIII makes it easy to backup your database withor without all the data. Just highlite the database you like to backup and choose the ''backup'' option from the main menu.
 
  
=ODBC, Postgres and Openoffice=
+
I edited the '''pg_hba.conf''' (the security settings) according to a debian/ubuntu setup. Delete everything in the original file and usethis content:
According to [http://de.openoffice.org/doc/howto_2_0/base/ooopsql.html PostgreSQL-Datenbanken in OpenOffice.org 2.0 unter Linux].
+
vim pg_hba.conf
  
==Ubuntu Gutsy (7.10)==
+
  # TYPE  DATABASE        USER    IP-ADDRESS      IP-MASK        METHOD
===Openoffice <-> ODBC===
+
local  all            all                                    trust
For connection between openoffice and odebc:
 
  apt-cache search odbc | grep unix
 
    unixodbc - ODBC tools libraries
 
    unixodbc-bin - Graphical tools for ODBC management and browsing
 
 
   
 
   
  apt-get install unixodbc unixodbc-bin
+
  # Connections for PCs on the subnet. Add a line for each pc or network
 +
# that want's to connect to the server.
 +
# TYPE  DATABASE USER  IP-ADDRESS      IP-MASK        METHOD
 +
#host  all    all    192.168.0.0/24                  trust
 +
#host  all    all    192.168.0.100  255.255.255.255 trust
 +
host    all    all    127.0.0.1      255.255.255.255 trust
  
With the command '''ODBCConfig''' you can manage the configuration files (part of the package unixodbc-bin). Start it as superuser with ''gksu'' or use it to manage your local database configuration files - stored in your home directory.
+
Please alter the settings for accessing the database through other pc's of the network accordingly.
 +
The server ''192.168.0.100'' is just a sample! PostgreSQL on openSuSE is not configured for ''ident'' authentification method, like in on a ''debian'' system.
  
But for nowe, we will - later - edit the configuration files by ourself.
+
'''/var/lib/pgsql/share/postgresql.conf'''
  
===ODBC <-> Postgresql===
+
For the postgres server settings file '''postgresql.conf''' alter the file according to the following settings. Don't forget to add you servers ip to the ''listen_adresses'', if you like access your server from the outside.
 +
vim postgresql.conf
  
For connection between the odbc layer and the database:
+
[...]
  apt-cache search odbc
+
# Next: ADD YOUR IP, comma separeted list ...
    odbc-postgresql - ODBC driver for PostgreSQL
+
listen_addresses = 'localhost,192.168.0.1'
 +
  port = 5432
 +
max_connections = 100
 +
#ssl = on                    # SERVER DOES NOT START WITH 'ON'
 +
password_encryption = on
 +
shared_buffers = 24MB
 +
 
 +
log_destination = 'stderr'
 +
redirect_stderr = on
 +
#silent_mode = on            # Please UNCOMMENT !!!
 +
log_line_prefix = '%t %d %u '
 
   
 
   
  apt-get install odbc-postgresql
+
  stats_start_collector = on
 +
stats_row_level = on
 +
autovacuum = on
 +
default_transaction_read_only = off
 +
 +
datestyle = 'iso, dmy'
 +
lc_messages = 'de_DE.UTF-8'
 +
lc_monetary = 'de_DE.UTF-8'
 +
lc_numeric = 'de_DE.UTF-8'
 +
lc_time = 'de_DE.UTF-8'
 +
 
 +
==Open the Firewall Port==
 +
If you like to access the server from other pc's add the port number ''5432'' (opensuse 10.1 & 10.2) or ''postgresql server'' to the allowed services. You can do this with YaST.
 +
 
 +
==Add server start to runlevel==
 +
After the installation of ''postgresql-server'', it is not started automatically on boot.
 +
 
 +
Open the ''runlevel editor'' in YaST. Choose ''expert mode'' and choose to run the server e.g. in the runlevels 2, 3 and 5.
 +
 
 +
==Restart Server==
 +
rcpostgresql restart
  
===Configuration files===
+
Now you should be able to connect to the database via ''localhost'' and ''your IP adress'' (if added in the ''pg_hba.conf'') via pgadmin.
Ubuntu installs its '''odbc libraries''' in ''/usr/lib/odbc''. There are two versions of the postgres odbc driver: an ascii version (psqlodbca.so) and a unicode version (psqlodbcw.so). We are using the latter.
 
vim /etc/odbcinst.ini
 
  
[PostgreSQL]
+
=Administration with Pgadmin III=
Description = PostgreSQL ODBC Driver
+
Pgadmin helps you with almost all administration tasks to manage your postgresql databases. From managing user accounts (roles), creating tables, schemes, constraints (e.g. primary keys) to an extended sql editor.
Driver = /usr/lib/odbc/psqlodbcw.so
 
Setup = /usr/lib/odbc/libodbcpsqlS.so
 
  
For '''system wide database location''' you have to edit the ''/etc/odbc.ini'' file.
+
===Initial registration with pgAdminIII===
 +
To be able to manage your database with pgadmin you have to configure it for the specific server:
  
There is a section ''Driver''. You have to insert ''exactly'' the same name as defined in the ''odbcinst.ini'' between the braces, e.g. use ''PostgreSQL'', if your ''odbcinst.ini'' is defined as ''[PostgreSQL]'':
+
[[Image:PdAdminIII_initial_registration.png|400px|Working with pgAdminIII ]]
vim /etc/odbc.ini
 
  
[pgTestDBSource]
+
===Queries with pgadminIII===
Description = PostgreSQL Test Database
+
A very good tool for working with the database is the integrated '''sql query editor''' of ''pgadmin''. Select the database you want to work with, then you can choose the query tool from the menu.
Driver = PostgreSQL
 
Servername = localhost
 
Database = pgtest
 
Port = 5432
 
ReadOnly = No
 
  
===Establish connection===
+
PostgreSQL queries are ''case sensitive''. So if you specified '''U'''ppercase names for ''columns'' you have to use them '''exactly''' and in ''quotes''. It might be necessary to add the ''schemata'' name postfixed by a '''dot''' to get a proper query:
If you like to connect to an database via openoffice you should use a string like:
+
  select "Prename" from myscheme."Client";
  odbc://servername/databasename
+
 
 +
Press <F5> to start the query.
 +
 
 +
You can easily save the queries in a file for later reuse.
  
Don't forget to allow connections in ''/etc/postgresql/8.2/main/pg_hba.conf'' (see above) to your database server!
+
===Backup and restore  with pgadminIII===
 +
PgadminIII makes it easy to backup your database with or without all the data. Just highlite the database you like to backup and choose the ''backup'' option from the main menu.
  
The rest is really self explanatory within the openoffice database assistant ;)
+
[[Category:Database]]
 +
[[Category:Ubuntu]]

Latest revision as of 18:20, 29 April 2017

General

This guide is tested on ubuntu feisty with postgresql 8.2. It should work for former and future versions of both too.

General documentation you will find here:

You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles).

User Management

See the excellent online documentation:

Common Tasks

mysql: CREATE DATABASE

postgresql: CREATE DATABASE db_name OWNER username;
postgresql: 

mysql: SHOW DATABASES

postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: USE DATABASE

postgresql: \c databsename

mysql: SHOW TABLES

postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW COLUMNS

postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

Copy Database

su postgres
psql
postgres=# \l  
                                          List of databases  
    Name     |     Owner     | Encoding |  Collation  |    Ctype    |   Access privileges     
-------------+---------------+----------+-------------+-------------+-----------------------  
mydb         | name_of_owner | UTF8     | en_US.UTF-8 | en_US.UTF-8 |   
postgres=# CREATE DATABASE newdb WITH TEMPLATE mydb OWNER name_of_owner;

Ubuntu 12.04 and above

Alle major versions of postgresql are supported directly from the following site:

Create and enable a new sources.list file

sudo vim /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/VERSION-pgdg main

with VERSION is presice, trusty, ....

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-8.4


Ubuntu Feisty, Gutsy, Hardy, ..., Maverick (8.4)

Backup

Also see next section "Upgrade".

From here I have the following idea:

Dump Postgres Database using ssh. Use pg_dump command command:

pg_dump -U USERNAME YOUR-DATABASE-NAME | \
ssh user@remote.server.com "dd of=/pgsql/$(date +'%d-%m-%y')"

Upgrade - dump and restore

Generally look at

Upgrading from major versions (e.g. 8.1 to 8.3) is done via an database dump/restore cycle:

Dump...

su postgres
pg_dumpall > /path/to/all_pgsql_db_dump
pg_dumpall | gzip -c > /path/to/all_pgsql_db_dump.gz

and restore a single database sudo -u postgres psql database_to_restore < all_pgsql_db_dump

or everything:

sudo -u postgres psql < /path/to/all_pgsql_db_dump

According to this blog entry the following is easy using the cluster upgrade method:pg_upg

"On debian / ubuntu, it's easy to do and there's a command pg_upgradecluster that takes as argument the versions and the locations of the two clusters (a pg installation is called a cluster) and then initdbs the new one like the old one, and initiates a pg_dumpall from the old to the new and voila, you're migrated. Really, it's pretty seamless."

Installation

Install postgresql (version 8.3 for ubuntu hardy / 8.04)

VER=8.3; sudo apt-get install postgresql-$VER postgresql-client-$VER

If postgesql won't start due to locale settings please do either:

  • edit your SERVER sshd_config file
> vim /etc/ssh/sshd_config 
# Allow client to pass locale environment variables
# AcceptEnv LANG LC_*
  • edit your CLIENT ssh_config file:
> vim /etc/ssh/sshd_config
#    SendEnv LANG LC_*
  • add this at the end of your /etc/profile
> vim /etc/profile 
[...]
export LANGUAGE="en"
export LANG="C"
export LC_MESSAGES="C"

Postgres, Mysql, php5, apache2

Don't forget to install the appropriate dbo's:

apt-get install php5-pgsql php5-mysql

Pgadmin3

Do not use on server environment in the internet, and open the ports to outside access!!! Using VPN and a firewall is no problem.

For administration we install pgadmin3

sudo apt-get install pgadmin3 pgadmin3-data

Set database admin account

Alter the password for the standard postgres user account (as root)

sudo su postgres -c psql template1

Inside the database:

template1=# ALTER USER postgres WITH PASSWORD 'new_password';
template1=# \q

Alter the unix user postgres

sudo passwd -d postgres
sudo su postgres -c passwd

Enable the admin pack for use with pgadmin3

VER="8.3"; sudo apt-get install postgresql-contrib-$VER
sudo su postgres -c psql < /usr/share/postgresql/${VER}/contrib/adminpack.sql

Ubuntu Configuration files

/etc/postgresql/8.2/main/postgresql.conf

Assure that the server is accessible on localhost and from the servers ip.

VER="8.3"; sudo gedit /etc/postgresql/${VER}/main/postgresql.conf
# - Connection Settings -
# what IP address(es) to listen on; 
# comma-separated list of addresses;
listen_addresses = 'localhost,server_ip'
password_encryption = on

/etc/postgresql/VERSION/main/pg_hba.conf

Security and server settings. If you like to use the server in a network you have to pay attention to three points:

  1. Add the server ip to the listen adress
  2. Allow special clients to connect
  3. Think about using ssl, if you connect through the internet
VER="8.3"; sudo gedit /etc/postgresql/${VER}/main/pg_hba.conf
VER="8.4"; sudo gedit /etc/postgresql/${VER}/main/pg_hba.conf

For Maverick (8.4) the following (and only those are necessary):

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# e.g. host all all 192.168.0.0/24 md5
# e.g. host all all 192.168.0.10 255.255.255.0 md5
host all all [ip address] [subnet mask] md5

For all other Ubuntu Versions additionally alter the following entries:

# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# Database administrative login by UNIX sockets
local all all trust

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

Restart postgresql

VER="8.3"; sudo /etc/init.d/postgresql-$VER restart

Maverick (8.4):

VER="8.4"; sudo /etc/init.d/postgresql restart

openSuSE 10.1, 10.3

SuSE oh SuSE ... why not just simply as debian, redhat & Co. ... !?

First I have to mention that it is almost impossible to get a howto on postgresql configuration on openSuSE. But what made me really angry was, that the sample files delivered with opensuse are completely useless to get a running system. E.g. the localisation settings of the server do not default to unicode.

Not even a search at support database of SuSE or Novell's support database gave me ANY useful result. This is really poor. For an example see this thread. Finding an according howto for ubuntu and getting startet with the server and pgadmin was a matter of minutes.

Second it is not good to change the location and content (!) of configuration files from version to version (10.1 -> 10.3) as this is the case! SuSE should follow the example of debian or redhat. A database system is not a playground for such exeriments and really NOT necessary.

Third without any comment: why does postgres on SuSE 10.3 not use utf-8 by default any more as it did on 10.1 !?

One could draw the conclusion, that NOVELL(former SuSE)-users(!) have not much experience with postgresql databases, compared with debian. This is not very appeasing if you consider using this in a sensible environment with the default configuration.

I am sorry to say, but setting up postgresql on a debian/ubuntu system that supported postgres from its beginning might be a better choice not just for inexperienced users.

Installation of postgresql-server

This is easily done via yast or zypper

zypper install postgresql-server

Administration

Pgadmin III

Pgadmin is good for managing the database server after it is configured and running. The editing of the configuration files should be made by hand - doiing it with pgadmin is a little bit complicated.

  • Install prior to opensuse 10.3
    • via YaST and the guru repository
  • opensuse 10.3 repositories and later do not contain pgadmin any more

Lack of administration module

Later, when you log into the database, pgadmin and try to click on the postgres system database, pgadmin complains about lacking the adminpack contrib module.

There is a package postgresql-contrib that includes additional modules for the server. But the postgres modules have to be configured separately - according to the postgresql documentation.

This is no problem, when working with other databases than postgres!

Post Installation

Start the server for the first time:

rcpostgresql start

Login as user postgres and alter the table. Instead of using the template1 database like in ubuntu/debian

su postgres -c psql postgres
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# \q

openSuSE configuration files

Confusing:

  • all configuration files are owned by postgres:postgres and placed in
/var/lib/pgsql/share
  • all sample configuration files are owned by root:root and placed in
/usr/share/postgresql

Copy the /usr/share/postgresql/*.conf.sample files to /var/lib/pgsql/share/*.conf and alter their permissions/owner:

(SuSE 10.3) cd /var/lib/pgsql/share
(SuSE 10.1) cd /var/lib/pgsql/data 

The next is for 10.3 only:

(suSE 10.3 only !)
cp /usr/share/postgresql/pg_hba.conf.sample pg_hba.conf
cp /usr/share/postgresql/postgresql.conf.sample postgresql.conf
chown postgres:postgres pg_hba.conf postgresql.conf
chmod 600 postgresql.conf pg_hba.conf postgresql.conf

/var/lib/pgsql/share/pg_hba.conf

I edited the pg_hba.conf (the security settings) according to a debian/ubuntu setup. Delete everything in the original file and usethis content:

vim pg_hba.conf
# TYPE  DATABASE        USER    IP-ADDRESS      IP-MASK         METHOD
local   all             all                                     trust

# Connections for PCs on the subnet. Add a line for each pc or network
# that want's to connect to the server.
# TYPE  DATABASE USER   IP-ADDRESS      IP-MASK         METHOD
#host   all     all     192.168.0.0/24                  trust
#host   all     all     192.168.0.100   255.255.255.255 trust
host    all     all     127.0.0.1       255.255.255.255 trust

Please alter the settings for accessing the database through other pc's of the network accordingly. The server 192.168.0.100 is just a sample! PostgreSQL on openSuSE is not configured for ident authentification method, like in on a debian system.

/var/lib/pgsql/share/postgresql.conf

For the postgres server settings file postgresql.conf alter the file according to the following settings. Don't forget to add you servers ip to the listen_adresses, if you like access your server from the outside.

vim postgresql.conf
[...]
# Next: ADD YOUR IP, comma separeted list ...
listen_addresses = 'localhost,192.168.0.1'
port = 5432
max_connections = 100
#ssl = on                    # SERVER DOES NOT START WITH 'ON'
password_encryption = on
shared_buffers = 24MB
log_destination = 'stderr'
redirect_stderr = on
#silent_mode = on            # Please UNCOMMENT !!!
log_line_prefix = '%t %d %u '

stats_start_collector = on
stats_row_level = on
autovacuum = on
default_transaction_read_only = off

datestyle = 'iso, dmy'
lc_messages = 'de_DE.UTF-8'
lc_monetary = 'de_DE.UTF-8'
lc_numeric = 'de_DE.UTF-8'
lc_time = 'de_DE.UTF-8'

Open the Firewall Port

If you like to access the server from other pc's add the port number 5432 (opensuse 10.1 & 10.2) or postgresql server to the allowed services. You can do this with YaST.

Add server start to runlevel

After the installation of postgresql-server, it is not started automatically on boot.

Open the runlevel editor in YaST. Choose expert mode and choose to run the server e.g. in the runlevels 2, 3 and 5.

Restart Server

rcpostgresql restart

Now you should be able to connect to the database via localhost and your IP adress (if added in the pg_hba.conf) via pgadmin.

Administration with Pgadmin III

Pgadmin helps you with almost all administration tasks to manage your postgresql databases. From managing user accounts (roles), creating tables, schemes, constraints (e.g. primary keys) to an extended sql editor.

Initial registration with pgAdminIII

To be able to manage your database with pgadmin you have to configure it for the specific server:

Working with pgAdminIII

Queries with pgadminIII

A very good tool for working with the database is the integrated sql query editor of pgadmin. Select the database you want to work with, then you can choose the query tool from the menu.

PostgreSQL queries are case sensitive. So if you specified Uppercase names for columns you have to use them exactly and in quotes. It might be necessary to add the schemata name postfixed by a dot to get a proper query:

select "Prename" from myscheme."Client";

Press <F5> to start the query.

You can easily save the queries in a file for later reuse.

Backup and restore with pgadminIII

PgadminIII makes it easy to backup your database with or without all the data. Just highlite the database you like to backup and choose the backup option from the main menu.