Difference between revisions of "PostgreSQL"

From DUNE
Jump to navigation Jump to search
 
(30 intermediate revisions by the same user not shown)
Line 6: Line 6:
 
</pre>
 
</pre>
  
−
You will likely need an additional package if using
+
You will likely need an additional package if using PostgreSQL as the Django backend:
−
PostgreSQL as the Django backend:
 
 
<pre>
 
<pre>
 
sudo pip3 install psycopg2
 
sudo pip3 install psycopg2
 +
# or:
 +
sudo pip3 install psycopg2-binary
 +
</pre>
 +
 +
If you don't already have pip3 installed, there is some footwork you'll need to follow
 +
as documented on the [[Linux Tools]] page.
 +
 +
Default location of the data files:
 +
<pre>
 +
/var/lib/postgresql/9.5/main
 +
</pre>
 +
 +
Uninstall:
 +
<pre>
 +
# check the directories first:
 +
dpkg -l | grep postgres
 +
 +
# now remove all components:
 +
sudo apt-get --purge remove postgresql # etc, for each component
 +
</pre>
 +
 +
 +
On Ubuntu 17.04 and up version 9.6 comes as a default, but for lower versions of Ubuntu
 +
PostgreSQL needs to be installed separately if version 9.6 is desired:
 +
 +
<pre>
 +
sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
 +
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 +
sudo apt-get update
 +
sudo apt-get install postgresql-9.6
 
</pre>
 
</pre>
  
Line 17: Line 46:
 
sudo yum install postgresql-server postgresql-contrib
 
sudo yum install postgresql-server postgresql-contrib
 
sudo pip3 install psycopg2
 
sudo pip3 install psycopg2
 +
</pre>
 +
 +
==Red Hat==
 +
<pre>
 +
sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm # reference to repo
 +
# or:
 +
sudo rpm -Uvh http://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
 +
# then
 +
sudo yum install postgresql96-server postgresql96
 +
</pre>
 +
 +
==Find Version==
 +
<pre>
 +
SELECT version();
 
</pre>
 
</pre>
  
Line 35: Line 78:
  
 
Start the service:
 
Start the service:
 +
<pre>sudo systemctl start postgresql</pre>
 +
 +
To enable automatic start on reboot:
 +
<pre>sudo systemctl enable postgresql</pre>
 +
 +
Restarting can be a little tricky
 +
<pre>
 +
sudo -u  postgres pg_ctl restart -D /var/lib/pgsql/data
 +
</pre>
 +
 +
==Red Hat==
 +
<pre>
 +
/usr/pgsql-9.6/bin/postgresql96-setup initdb
 +
</pre>
 +
 +
https://tecadmin.net/install-postgresql-on-centos-rhel-and-fedora/
 +
 +
==Strict Service Name==
 +
Some installations require the exact service name to be specified
 +
when restarting etc. Example:
 
<pre>
 
<pre>
−
sudo systemctl start postgresql
+
$ systemctl list-units|grep postgresql
−
sudo systemctl enable postgresql
+
postgresql-9.6.service
 
</pre>
 
</pre>
  
−
Restarting can be a little tricky
+
So in this case one needs to run:
 
<pre>
 
<pre>
−
sudo -u  postgres pg_ctl restart -D /var/lib/pgsql/data
+
sudo service postgresql-9.6.service restart
 
</pre>
 
</pre>
  
Line 49: Line 112:
 
The "psql" client requires a semicolon after each string you enter on
 
The "psql" client requires a semicolon after each string you enter on
 
the command line. It won't report if it's missing and it's easy to forget.
 
the command line. It won't report if it's missing and it's easy to forget.
 +
This doesn't include the "backslash commands" (see below).
  
 
Switch over to the postgres account on your server by typing:
 
Switch over to the postgres account on your server by typing:
 
<pre>
 
<pre>
 
sudo -i -u postgres
 
sudo -i -u postgres
 +
# or, if sudo configuration is restrictive
 +
sudo su - postgres
 +
# then
 
psql
 
psql
−
</pre>
+
#
−
 
+
###### do same as above without switching accounts (just switching for one session):
−
Same without switching accounts (just switching for one session):
+
#
−
<pre>
 
 
sudo -u postgres psql
 
sudo -u postgres psql
 +
# whether this is allowed by sudo will depend on the system
 
</pre>
 
</pre>
  
Line 67: Line 134:
 
connect to PostgreSQL not as the default "postgres" user but for example
 
connect to PostgreSQL not as the default "postgres" user but for example
 
as "p3s" or any other userID of choice:
 
as "p3s" or any other userID of choice:
 +
 
<pre>
 
<pre>
 
psql -U p3s -d tst
 
psql -U p3s -d tst
 +
# or, as in the example above when the accounts are switched just for one command:
 +
sudo -u postgres psql -d tstOrOtherDatabaseOfChoice
 
</pre>
 
</pre>
  
−
The "-d" option is important because otherwise psql will assume a default
+
The "-d" option may be important because otherwise psql may assume a default
 
database name which may not in fact exist. In the above example, the "tst"
 
database name which may not in fact exist. In the above example, the "tst"
 
database was created beforehand by the user "postgres" to enable testing.
 
database was created beforehand by the user "postgres" to enable testing.
 +
 +
Alternatively, one can connect to an existing database after logging in by
 +
using the "\c" shortcut i.e.
 +
<pre>
 +
\c tst
 +
</pre>
 +
...or any other existing database within the system.
 +
 +
Execution of a SQL script contained in a file is achieved by utilizing the "-f"
 +
option e.g.
 +
<pre>
 +
psql -U p3s -d tst -f myCoolSqlScript.sql
 +
</pre>
  
 
Example of getting help:
 
Example of getting help:
Line 82: Line 165:
  
 
Exit out of the PostgreSQL prompt by typing: \q
 
Exit out of the PostgreSQL prompt by typing: \q
 +
 +
==Pager==
 +
Many PostgreSQL installation will use a paging program when displaying the output of "select"
 +
in the user's terminal window, and it may be something like "vi". This is not helpful at times
 +
e.g. when you run a macro. This can be avoided by deactivating the pager, and optionally setting
 +
the "extended" option on:
 +
<pre>
 +
\x on
 +
\pset pager off
 +
select * from myTable
 +
</pre>
  
 
==Users==
 
==Users==
 +
List users:
 +
<pre>
 +
\du
 +
</pre>
 +
 
Create a user/role:
 
Create a user/role:
 
<pre>
 
<pre>
Line 94: Line 193:
 
</pre>
 
</pre>
  
−
==Databases and Tables==
+
Password:
 +
<pre>
 +
alter user FOO password 'BAR';
 +
</pre>
 +
 
 +
==Databases, Schema and Tables==
 
===Creation of DB===
 
===Creation of DB===
 
From the OS prompt:
 
From the OS prompt:
Line 104: Line 208:
 
<pre>
 
<pre>
 
postgres=# create database testdb;
 
postgres=# create database testdb;
 +
</pre>
 +
 +
===Schemas===
 +
These are akin to namespaces. The tables will be looked up only in those whose name is in the "search_path", so if you are puzzled that some of legit tables aren't visible to you do something like
 +
<pre>
 +
# for a user:
 +
ALTER ROLE <your_login_role> SET search_path TO a,b,c;
 +
# for the whole database:
 +
ALTER DATABASE <database_name> SET search_path TO schema1,schema2;
 
</pre>
 
</pre>
  
Line 112: Line 225:
 
testdb(# age int not null
 
testdb(# age int not null
 
testdb(# );
 
testdb(# );
 +
</pre>
 +
 +
Another example:
 +
 +
<pre>
 +
CREATE TABLE account(
 +
user_id serial PRIMARY KEY,
 +
username VARCHAR (50) UNIQUE NOT NULL,
 +
password VARCHAR (50) NOT NULL,
 +
email VARCHAR (355) UNIQUE NOT NULL,
 +
created_on TIMESTAMP NOT NULL,
 +
last_login TIMESTAMP
 +
);
 
</pre>
 
</pre>
  
Line 133: Line 259:
 
\d
 
\d
 
</pre>
 
</pre>
 +
 +
Info about a table:
 +
<pre>
 +
\d table_name
 +
</pre>
 +
More detail:
 +
<pre>
 +
\d+ table_name
 +
</pre>
 +
 +
Locks
 +
<pre>
 +
SELECT * FROM pg_locks
 +
</pre>
 +
 +
===Grant===
 +
<pre>
 +
Create a read-only user in PostgreSQL
 +
First, grant connect access:
 +
 +
GRANT CONNECT ON DATABASE table_name TO username;
 +
Then grant usage on schema
 +
 +
GRANT USAGE ON SCHEMA public TO username;
 +
For a specific table
 +
 +
GRANT SELECT ON table_name TO username;
 +
For multiple tables
 +
 +
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
 +
If you want to grant access to the new table in the future automatically, you have to alter default:
 +
 +
ALTER DEFAULT PRIVILEGES IN SCHEMA public
 +
GRANT SELECT ON TABLES TO username;
 +
 +
</pre>
 +
 +
=Restoring from a dump file=
 +
<pre>
 +
pg_restore -d myDB -U user DUMP_FILE_NAME
 +
</pre>
 +
 +
The user needs to have right privileges to the database.
 +
  
 
=Remote Access and Authorization Mode=
 
=Remote Access and Authorization Mode=
−
Add or edit the following line in your postgresql.conf, in order to enable access
+
The firewall settings need to be checked. For example, on a recent Ubuntu distribution one may
 +
do the following in order to open the PostgreSQL port:
 +
<pre>
 +
sudo ufw enable
 +
ufw allow 5432/tcp
 +
</pre>
 +
 
 +
Two configurations files are of relevance here:
 +
* postgresql.conf
 +
* pg_hba.conf
 +
 
 +
==Listen Addresses==
 +
Add or edit the following line in your '''postgresql.conf''', in order to enable access
 
from any host (edit accordingly for more selective access rights):
 
from any host (edit accordingly for more selective access rights):
 
<pre>
 
<pre>
 
listen_addresses = '*'
 
listen_addresses = '*'
 +
port = 5432
 
</pre>
 
</pre>
  
−
To enable authentication from remote hosts for user "foo", edit pg_hba.conf to add
+
The original lines are these:
 +
<pre>
 +
#listen_addresses = 'localhost'        # what IP address(es) to listen on;
 +
                                        # comma-separated list of addresses;
 +
                                        # defaults to 'localhost'; use '*' for all
 +
                                        # (change requires restart)
 +
#port = 5432                            # (change requires restart)
 +
</pre>
 +
 
 +
 
 +
==Remote Authentication==
 +
To enable authentication from remote hosts for user "foo", edit '''pg_hba.conf''' to add
 
<pre>
 
<pre>
 
host    all            foo            0.0.0.0/0              md5
 
host    all            foo            0.0.0.0/0              md5
Line 149: Line 343:
 
/etc/postgresql/9.5/main/pg_hba.conf # Ubuntu
 
/etc/postgresql/9.5/main/pg_hba.conf # Ubuntu
 
/var/lib/pgsql/data/pg_hba.conf # CentOS
 
/var/lib/pgsql/data/pg_hba.conf # CentOS
 +
/var/lib/pgsql/9.6/data/pg_hba.conf # Red Hat
 
</pre>
 
</pre>
  
 +
One can also query the database itself for the location of the file:
 +
<pre>
 +
psql -U postgres -c 'SHOW config_file' # may or may not work depending on local restrictions
 +
</pre>
  
−
Working example:
+
Working example of the authorization section:
 
<pre>
 
<pre>
 
local  all            postgres                                peer
 
local  all            postgres                                peer

Latest revision as of 01:54, 31 May 2019

Installation

Ubuntu/Debian

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

You will likely need an additional package if using PostgreSQL as the Django backend: 

sudo pip3 install psycopg2
# or:
sudo pip3 install psycopg2-binary

If you don't already have pip3 installed, there is some footwork you'll need to follow as documented on the Linux Tools page.

Default location of the data files: 

/var/lib/postgresql/9.5/main

Uninstall: 

# check the directories first:
dpkg -l | grep postgres

# now remove all components:
sudo apt-get --purge remove postgresql # etc, for each component


On Ubuntu 17.04 and up version 9.6 comes as a default, but for lower versions of Ubuntu PostgreSQL needs to be installed separately if version 9.6 is desired: 

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

CentOS

sudo yum install postgresql-server postgresql-contrib
sudo pip3 install psycopg2

Red Hat

sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm # reference to repo
# or:
sudo rpm -Uvh http://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
# then
sudo yum install postgresql96-server postgresql96

Find Version

SELECT version();

Running

Ubuntu/Debian

If a restart of the DB engine is required: 

sudo service postgresql restart

Configuration will be defined in files contained in /etc/postgresql

CentOS

Before first use: 

sudo postgresql-setup initdb

Start the service: 

sudo systemctl start postgresql

To enable automatic start on reboot: 

sudo systemctl enable postgresql

Restarting can be a little tricky 

sudo -u  postgres pg_ctl restart -D /var/lib/pgsql/data

Red Hat

/usr/pgsql-9.6/bin/postgresql96-setup initdb

https://tecadmin.net/install-postgresql-on-centos-rhel-and-fedora/

Strict Service Name

Some installations require the exact service name to be specified when restarting etc. Example: 

$ systemctl list-units|grep postgresql
postgresql-9.6.service

So in this case one needs to run: 

sudo service postgresql-9.6.service restart

PSQL

Log in/out on localhost

The "psql" client requires a semicolon after each string you enter on the command line. It won't report if it's missing and it's easy to forget. This doesn't include the "backslash commands" (see below).

Switch over to the postgres account on your server by typing: 

sudo -i -u postgres
# or, if sudo configuration is restrictive
sudo su - postgres
# then
psql
#
###### do same as above without switching accounts (just switching for one session):
#
sudo -u postgres psql
# whether this is allowed by sudo will depend on the system


After having created a user and making sure authentication method is set correctly in the configuration file (path may be system and version dependent and named something like /etc/postgresql/9.5/main/pg_hba.conf), one can connect to PostgreSQL not as the default "postgres" user but for example as "p3s" or any other userID of choice: 

psql -U p3s -d tst
# or, as in the example above when the accounts are switched just for one command:
sudo -u postgres psql -d tstOrOtherDatabaseOfChoice

The "-d" option may be important because otherwise psql may assume a default database name which may not in fact exist. In the above example, the "tst" database was created beforehand by the user "postgres" to enable testing.

Alternatively, one can connect to an existing database after logging in by using the "\c" shortcut i.e. 

\c tst

...or any other existing database within the system.

Execution of a SQL script contained in a file is achieved by utilizing the "-f" option e.g. 

psql -U p3s -d tst -f myCoolSqlScript.sql

Example of getting help: 

testdb=# \h create table


Exit out of the PostgreSQL prompt by typing: \q

Pager

Many PostgreSQL installation will use a paging program when displaying the output of "select" in the user's terminal window, and it may be something like "vi". This is not helpful at times e.g. when you run a macro. This can be avoided by deactivating the pager, and optionally setting the "extended" option on: 

\x on
\pset pager off
select * from myTable

Users

List users: 

\du

Create a user/role: 

createuser --interactive

Another example: 

create user FOO with SUPERUSER

Password: 

alter user FOO password 'BAR';

Databases, Schema and Tables

Creation of DB

From the OS prompt: 

sudo -u postgres createdb foo

Also can be done from within psql. 

postgres=# create database testdb;

Schemas

These are akin to namespaces. The tables will be looked up only in those whose name is in the "search_path", so if you are puzzled that some of legit tables aren't visible to you do something like 

# for a user:
ALTER ROLE <your_login_role> SET search_path TO a,b,c;
# for the whole database:
ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Tables

testdb=# create table people (
testdb(# name char(50) primary key not null,
testdb(# age int not null
testdb(# );

Another example: 

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

Changing a table: 

ALTER TABLE foo ADD last_maint date;


Info

List of DBs: 

\l

List of schemas: 

\d

Info about a table: 

\d table_name

More detail: 

\d+ table_name

Locks 

SELECT * FROM pg_locks

Grant

Create a read-only user in PostgreSQL
First, grant connect access:

GRANT CONNECT ON DATABASE table_name TO username;
Then grant usage on schema

GRANT USAGE ON SCHEMA public TO username;
For a specific table

GRANT SELECT ON table_name TO username;
For multiple tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
If you want to grant access to the new table in the future automatically, you have to alter default:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;

Restoring from a dump file

pg_restore -d myDB -U user DUMP_FILE_NAME

The user needs to have right privileges to the database.


Remote Access and Authorization Mode

The firewall settings need to be checked. For example, on a recent Ubuntu distribution one may do the following in order to open the PostgreSQL port: 

sudo ufw enable
ufw allow 5432/tcp

Two configurations files are of relevance here:

  • postgresql.conf
  • pg_hba.conf

Listen Addresses

Add or edit the following line in your postgresql.conf, in order to enable access from any host (edit accordingly for more selective access rights): 

listen_addresses = '*'
port = 5432

The original lines are these: 

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)


Remote Authentication

To enable authentication from remote hosts for user "foo", edit pg_hba.conf to add 

host    all             foo             0.0.0.0/0               md5

This file can be found in locations like 

/etc/postgresql/9.5/main/pg_hba.conf # Ubuntu
/var/lib/pgsql/data/pg_hba.conf # CentOS
/var/lib/pgsql/9.6/data/pg_hba.conf # Red Hat

One can also query the database itself for the location of the file: 

psql -U postgres -c 'SHOW config_file' # may or may not work depending on local restrictions

Working example of the authorization section: 

local   all             postgres                                peer
local   all             p3s                                     md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
Retrieved from "https://dune.bnl.gov/w/index.php?title=PostgreSQL&oldid=2431"