PostgreSQL
Contents
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.
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
The "-d" option is important because otherwise psql will 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.
Example of getting help:
testdb=# \h create table
Exit out of the PostgreSQL prompt by typing: \q
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(# );
Changing a table:
ALTER TABLE foo ADD last_maint date;
Info
List of DBs:
\l
List of schemas:
\d
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
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)
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
One can also query the database itself for the location of the file:
psql -U postgres -c 'SHOW config_file'
Working example:
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
