PostgreSQL

From Limbas Wiki

Jump to: navigation, search
This page is a translated version of the page PostgreSQL and the translation is 100% complete.

<-- backt to Main Page


PostgreSQL Installation for Limbas

Limbas supports ISO as well as UTF8 Encodings. Limbas converts the date and time formats independently and requires the date format "DD.MM.YYYY". Since PostgreSQL is installed with localization as standard, either the record cluster or the record without localization has to be created.


Create/Renew Database Cluster

- Stop postgres server (root)

/etc/init.d/postgresql stop

- Delete old cluster:

Directory content (cluster) of "/var/lib/pgsql/data" has to be deleted (Please notice: Existing databases will be deleted !)

- Create cluster without localisation:
Basically you should not use localisation because of less performance. Be aware that Limbas expect the dateformat in 'YY-MM-DD'. If you have trouble with special digits you can use the ctype setting.

su - postgres
initdb --local=C
[ initdb --local=C --lc-ctype=de_DE ]
exit


Start Server (root)

systemctl start postgresql.service

Create Database

Without localization. Possible encondings are for example LATIN1 or SQL_ASCII or UTF8

# su - postgres
# psql
# create user limbasuser password 'limbaspass';
# create database limbas WITH ENCODING 'SQL_ASCII' LC_COLLATE 'C' LC_CTYPE 'C' OWNER limbasuser TEMPLATE template0;
# \q
# createlang plpgsql limbas

Unix ODBC Setup

The database connection with ODBC is necessary if PostgreSQL or MYSQL is not used as a database or the PDO interface is not available. The setup of ODBC is more complex and requires not only the necessary packages, but also setting up UnixODB and the database ODBC driver. unixODBC

General Tools

Overview of all Databases

psql -l

Create User

createuser username

or with SQL Befehl: create user username password 'secret'

Delete USer

dropuser username

or with SQL Command: DROP USER name;

Database Creation over Console

su - postgres createdb -O username limbas

Delete Database

su - postgres
dropdb dbname

add plpgsql languge

createlang plpgsql limbas

Restart Server

pg_ctl reload


To dump a database:

$ pg_dump mydb > db.out

To reload this database:

$ psql -d database -f db.out

To dump a database called mydb that contains large objects to a tar file:

$ pg_dump -Ft -b mydb > db.tar

To reload this database (with large objects) to an existing database called newdb:

$ pg_restore -d newdb db.tar