Postgres SQL
From Blue-IT.org Wiki
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
General Dokumentation
- (GERMAN) Postgres on Selflinux.org
- (GERMAN) German Postgres Page
Ubuntu Feisty, Gutsy
- Thanks to the article on hocuspok.us, it was very easy to set up postgresql-8.2 running on ubuntu.
- Another Quicklink for ubuntu: PostgreSQL Quickstart
Installation
Install postgresql
sudo apt-get install postgresql-8.2 postgresql-client-8.2
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
Ubuntu Configuration files
/etc/postgresql/8.2/main/postgresql.conf
Assure that the server is accessible on localhost and from the servers ip.
sudo gedit /etc/postgresql/8.2/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/8.2/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:
- Add the server ip to the listen adress
- Allow special clients to connect
- Think about using ssl, if you connect through the internet
sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
# 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 # 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
sudo /etc/init.d/postgresql-8.2 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.
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
- Download the rpm package from the pgadmin site: Direct from pgadmin.org or pgadmin 10.3 and install them with a right mouse klick on the rpm file.
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:
cd /var/lib/pgsql/share 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_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:
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.