Wednesday, 10 May 2017

Postgresql

Postgresql

Here is a IMHO nice tutorial: http://www.postgresqltutorial.com

Installation

apt-cache search postgresql
apt-get install postgresql-9.4

Start/Stop

systemctl stop postgresql
systemctl start postgresql
systemctl status postgresql

Configuration

 Login as Postgresql Admin user:
 su - postgres

Move Data Directory

Show the data directory path in pgsql:
SHOW data_directory;
Shut down postgresql DBMS as root:
systemctl stop postgresql
Copy data to hd:
rsync -av /var/lib/postgresql /mnt/data/postgresql
Rename old data directory:
mv /var/lib/postgresql  /var/lib/postgresql.bak
Change  data_directory parameter in postgresql configuration file:
vi /etc/postgresql/9.4/main/postgresql.conf

Set data_directory to the new location:

 data_directory = '/mnt/data/postgresql/9.4/main'
Start postgresql DBMS as root:
systemctl start postgresq
 Change the home directory of user postgres to:
/mnt/data/postgresql

psql

psql commands

List psql commands:
\?
List databases:
\l
 List databases with tablespace: 
\l+
Connect to database:
\c dbname
List tables in the database: 
\dt
Show settings:
show data_directory;
The show command displays the content of configuration parameter. In this case the data directory is displayed.
/mnt/data/postgresql/9.4/main
List sql commands:
\h

Create test database

Start psql an enter this command:
create database mytestdb;
Connect to the new db:
\c mytestdb
Now the database mytestdb is available. In order to create a table enter a create table command:
create table mytesttable (id integer primary key, data char(32));