PostgreSQL is a popular open-source RDBMS included in Red Hat Enterprise Linux.
http://www.postgresqlformac.com/ provides a good Mac-packaged version, including some GUI tools (DB backup, user management, etc.).
- The extensive official PostgreSQL Interactive Manual
- The Red Hat Database Manual (covers PostgreSQL 7.2)
- An intro to Using Regular Expressions in PostgreSQL
- Some good MySQL/SQLite/PostgreSQL hints at http://scriptures.nephi.org/notes.php
MySQL to PostgreSQL command-line hints
MySQL's command-line tool mysql uses SQL-like syntax such as SHOW DATABASES; for RDBMS information. PostgreSQL takes a different approach in psql-- meta-commands that begin with a backslash and do not need to end with a semicolon, or reference virtual relations beginning with pg_. Here are some of the most common:
|MySQL mysql||PostgreSQL psql|
|SHOW COLUMNS;||\d table-name|
|SHOW PROCESSLIST;||SELECT * FROM pg_stat_activity;|
|USE db-name;||\c db-name|
Other meta-commands are explained in man psql or in the Red Hat Database Manual
ALTER USER davide WITH PASSWORD 'hu8jmn3';
Setting up a PostgreSQL server to accept network connections
The default PostgeSQL configuration on Red Hat Enterprise Linux will not accept network connections. Additionally, PostgeSQL has a complex host-based authentication mechanism to control access to databases. By default, PostgreSQL uses the operating system to provide the connecting username (ident authentication), so you have to be logged in as the local user postgres to connect as superuser.
The following commands enable network connections and add rules to the host-based authentication table (pg_hba.conf) and as an example allow xbrain_user to connect from any machine on the subnet 184.108.40.206. If you also want to log queries, edit the `log_statement` line. By default logs are in /var/lib/pgsql/data/pg_log/.
yum -y install postgresql-server chkconfig postgresql on /etc/init.d/postgresql initdb /etc/init.d/postgresql start # run the other commands as postgres user su - postgres cd /var/lib/pgsql/data mv postgresql.conf postgresql.conf.orig # tested with Red Hat Enterprise Linux 6 cat > postgresql.conf<<EOF # defaults grep '^[a-z]' postgresql.conf.orig > postgresql.conf datestyle = 'iso, mdy' default_text_search_config = 'pg_catalog.english' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting log_directory = 'pg_log' # directory where log files are written, log_filename = 'postgresql-%a.log' # log file name pattern, logging_collector = on # Enable capturing of stderr and csvlog log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will log_truncate_on_rotation = on # If on, an existing log file of the max_connections = 100 # (change requires restart) shared_buffers = 32MB # min 128kB # custom settings listen_addresses = '*' autovacuum = on checkpoint_completion_target = 0.9 checkpoint_segments= 32 effective_cache_size = 1GB #1/2 your RAM listen_addresses = '*' log_statement = 'ddl' # none, mod, ddl, all; look in /var/lib/pgsql/data/pg_log/ maintenance_work_mem= 256MB random_page_cost = 3.0 wal_buffers = 16MB EOF cat >/var/lib/pgsql/data/pg_hba.conf<<EOF # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all ident host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all xbrain_user 220.127.116.11 255.255.255.0 md5 EOF logout /etc/init.d/postgresql restart
For more information on pg_hba.conf, see the PostgreSQL manual section on Authentication Methods
Options for Postgres 8.1 in EL5 were slightly different:
cd /var/lib/pgsql/data cat > postgresql.conf<<EOF listen_addresses = '*' # defaults to 'localhost', '*' = all max_connections = 100 shared_buffers = 1000 # min 16 or max_connections*2, 8KB each redirect_stderr = on # Enable capturing of stderr into log log_directory = 'pg_log' # Directory where log files are written log_filename = 'postgresql-%a.log' # Log file name pattern. log_truncate_on_rotation = on # log_rotation_age = 1440 # Automatic rotation of logfiles log_rotation_size = 0 # lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting log_statement = 'ddl' # none, mod, ddl, all; look in /var/lib/pgsql/data/pg_log/ stats_command_string = true # allow "SELECT datname,current_query FROM pg_stat_activity" EOF
Next, we need to create a user, in this example with the createuser tool.
createuser --no-superuser --no-createrole --no-createdb --pwprompt xbrain_user # Enter password for new user: # Enter it again: # CREATE USER
After creating the user we test out the TCP/IP connection:
psql template1 -U xbrain_user -h localhost # Password: # template1=> \q
Edit postgresql.conf to include:
log_statement = 'all'
Then restart the server and look at the logs in /var/lib/pgsql/data/pg_log/.
There are three steps to enabling SSL encryption for all client/server communications:
- Generate server.key and server.crt in /var/lib/pgsql/data/
- Add "ssl = on" to postgresql.conf
- Add "hostssl" lines to pg_hba.conf and restart the server
You can test with code such as "Connecting to PostgreSQL via an Encrypted Connection using SSL and JDBC".
An important aspect of changing max_connections in postgresql.conf is the that postgres uses a semaphore array for each connection. Semaphore arrays are a kernel resource for inter-process communication (IPC), and on Red Hat by default there are just 128. If you attempt to configure more than around 128 Postgres connections you will get this error in pgstartup.log:
FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(5432127, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 2051).
You can check your setting with cat /proc/sys/kernel/sem and add a line like this to /etc/sysctl.conf and run "sysctl -p" to change it (the 4th parameter is number of arrays):
kernel.sem = 250 32000 32 2048
The command ipcs which will then show postgres using a whole bunch of semaphore arrays.
There are several methods, but this works:
export PGHOST=myhost.iths.org export PGDATABASE=mydb export PGUSER=my_pguser echo "$PGHOST:5432:$PGDATABASE:$PGUSER:superpass" >> $HOME/.pgpass chmod 0600 $HOME/.pgpass pg_dump --format=c $PGDATABASE > 20090522-$PGDATABASE.$$ dropdb $PGDATABASE pg_restore --create --dbname template0 20090522-$PGDATABASE.$$
If you are not a postgres superuser you will get harmless errors like ERROR: must be owner of schema public. If you are not changing your table definitions or functions you can use --data-only instead of --clean after truncating your tables:
pg_dump --format=c --data-only > 20090522-$PGDATABASE.$$ pg_dump --schema-only openclinica |grep "CREATE TAB" | sed -e 's;CREATE;TRUNCATE;g' -e 's;..$; CASCADE\;;' > tr psql < tr pg_restore 20090522-$PGDATABASE.$$ > sql # edit dump? psql < sql
The psql tool has many output options, including unaligned comma-separated output. For example:
db=# select * from "Family"; ID | name | num ----+------+----- 3 | abc | 123 5 | bar | 5 (2 rows) db=# \a \t \pset fieldsep ',' Output format is unaligned. Showing only tuples. Field separator is ",". db=# select * from "Family"; 3,abc,123 5,bar,5 6,PLE 1234,6
Two owners to one database
In postgres, only a db owner can DROP a table. To allow two or more users full access to a database, you GRANT the new user the role of the database owner. For example:
GRANT myuser TO newuser;
This is because all users in postgres are actually "roles." Alternatively you can create a new role and grant both the old and new users that role.
Do I need to restart or reload?
How to know if changing a particular postgresql.conf item requires a restart, or a reload, of PostgreSQL:
select name, setting, context from pg_settings where context = 'postmaster'; -- configs requiring postgresql reload select name, setting, context from pg_settings where context = 'sighup';