Postgresql
Jump to navigation
Jump to search
Contents
Reference
- Postgresql docs
- How To Install PostgreSQL 12 on CentOS 7 / CentOS 8 - January 2020
- Yum installation wiki
- pgAdmin4 documentation
- Install, Configure and Secure PostgreSQL 12 on Ubuntu 18.04
Syntax
- drill down into JSON objects stored in json datatype columns
SELECT id, collection, end_datetime, jsonb_path_query( content, '$.properties.proj\:projjson.bbox') FROM pgstac.items
PostgreSQL Client Applications
postgresql-12-setup
- /usr/pgsql-12/bin/postgresql-12-setup
Usage: ./postgresql-12-setup {initdb|check_upgrade|upgrade} [SERVICE_NAME] Script is aimed to help sysadmin with basic database cluster administration. The SERVICE_NAME is used for selection of proper unit configuration file; For more info and howto/when use this script please look at the docu file . The 'postgresql' string is used when no SERVICE_NAME is explicitly passed. Available operation mode: initdb Create a new PostgreSQL database cluster. This is usually the first action you perform after PostgreSQL server installation. check_upgrade Checks whether the old cluster can be upgraded to the new version or not. upgrade Upgrade PostgreSQL database cluster to be usable with new server. Use this if you upgraded your PostgreSQL server to newer major version (currently from 11 to 12).
initdb
- Installed to /usr/pgsql-12/bin/initdb
- "initdb initializes a PostgreSQL database cluster."
- Usage:
initdb [OPTION]... [DATADIR]
- If the data directory is not specified, the environment variable PGDATA is used.
- The PGDATA env var for the postgres user comes set as
PGDATA=/var/lib/pgsql/12/data
createdb
- Creates a new PostgreSQL database
- Wrapper around the SQL command
CREATE DATABASE
- The database user who executes this command becomes the owner of the new database
- A different owner can be specified via the -O option, if the executing user has appropriate privileges.
Command: CREATE DATABASE Description: create a new database Syntax: CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]
createuser
- Creates a new PostgreSQL user (or more precisely, a role).
- A role is an entity that can own database objects and have database privileges
- A role can be considered a “user”, a “group”, or both depending on how it is used.
- Wrapper around the SQL command
CREATE ROLE
- Only superusers and users with CREATEROLE privilege can create new users
- If you wish to create a new superuser, you must connect as a superuser, not merely with CREATEROLE privilege.
Command: CREATE ROLE Description: define a new database role Syntax: CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid
dropdb
- self explanitory
dropuser
- self explanitory
pg_basebackup
- Take a base backup of a PostgreSQL cluster
- Taken without affecting other clients to the database, and can be used both for point-in-time recovery and as the starting point for a log shipping or streaming replication standby servers
- Makes a binary copy of the database cluster files, while making sure the system is put in and out of backup mode automatically. * Backups are always taken of the entire database cluster
- It is not possible to back up individual databases or database objects
- For individual database backups, a tool such as pg_dump must be used
pg_dump
- A utility for backing up a PostgreSQL database.
- Extract a PostgreSQL database into a script file or other archive file
- Makes consistent backups even if the database is being used concurrently
- Does not block other users accessing the database (readers or writers)
- pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall
- Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql(1). Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.
- The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.
reindexdb
- A utility for rebuilding indexes in a PostgreSQL database
vacuumdb
- A utility for cleaning a PostgreSQL database
- Generates internal statistics used by the PostgreSQL query optimizer
Other command line utilities
- pg_dumpall
- pg_restore
- pg_isready -h localhost -p 5439
Installation, setup, and config
Postgresql 12
Yum installation
vim etc/yum.repos.d/CentOS-Base.repo
and addexclude=postgresql*
from base and updates section- Get repository rpm from repopackages page
wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum localinstall pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql12-server postgresql12-docs postgresql12-plpython3
- Actual libraries end up in /usr/pgsql-12
- These are symlinked to /etc/alternatives/
- These symlinks are symlinked to /usr/bin
- These are symlinked to /etc/alternatives/
Config
initdb
- Initialize DB for the first time:
/usr/pgsql-12/bin/postgresql-12-setup initdb
- Creates user on system named
postgres
- If the data directory is not specified, the environment variable PGDATA is used.
- The PGDATA env var for the postgres user comes set as
PGDATA=/var/lib/pgsql/12/data
- Creates user on system named
- Important files created:
- /etc/systemd/system/multi-user.target.wants/postgresql-12.service
- $DATA_DIR/postgresql.conf
- Configure where log files go, etc
- $DATA_DIR/pg_hba.conf - "PostgreSQL Client Authentication Configuration File"
2. Run as systemd service
- Start the server:
systemctl enable --now postgresql-12
- Check status:
systemctl status postgresql-12
- Check status:
3. Change superuser password
- Change UNIX user to postgres and change SuperUser password
su - postgres
psql -c "alter user postgres with password 'StrongPassword'"
pgadmin4 setup
/usr/local/bin/pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.18/pip/pgadmin4-4.18-py2.py3-none-any.whl
sudo python3 /usr/local/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
- Without sudo, eror message: PermissionError: [Errno 13] Permission denied: '/var/lib/pgadmin'
$ sudo python3 /usr/local/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py NOTE: Configuring authentication for SERVER mode. Enter the email address and password to use for the initial pgAdmin user account: Email address: your.email@gmail.com Password: Retype password: pgAdmin 4 - Application Initialisation ====================================== Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser. * Serving Flask app "pgadmin" (lazy loading) * Environment: production WARNING: Do not use the development server in a production environment. Use a production WSGI server instead. * Debug mode: off
Postgresql 9
- Initialize DB for the first time:
initdb /usr/local/var/postgres
- Start the server:
pg_ctl -D /usr/local/var/postgres/ -l /usr/local/var/postgres/server.log start
#Create a user, a database and add the PL/pgSQL language to your database. createuser -P -D -R -S db_user Enter password for new role: # db_password Enter it again: # db_password createdb -O db_user omero_database createlang plpgsql omero_database dropdb omero_database
user chris History
502 mvim .bash_profile 503 which psql 504 sudo rm `which psql` 505 ls /opt/local/bin/ | less 506 port contents postgresql90 507 which psql 508 port installed | grep postgresql 509 port contents postgresql_select 510 cat /opt/local/etc/select/postgresql/base 511 cat /opt/local/etc/select/postgresql/none 512 sudo port install postgresql90-server 513 which postgres 514 man mkdir 515 sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb 516 sudo chown -R postgres:postgres /opt/local/var/db/postgresql90 517 sudo mkdir -p /opt/local/var/log/postgresql90 518 sudo chown -R postgres:postgres /opt/local/var/log/postgresql90 519 sudo -u postgres /opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb 520 which postgres 521 sudo dscl . -create /Users/postgres UserShell /bin/bash 522 man dscl 523 dscl . -read /Users/postgres 524 dscl . -read /Groups/postgres 525 ls 526 mvim .bashrc 527 find /opt/local/ -name "pg_hba.conf" 528 sudo find /opt/local/ -name "pg_hba.conf" 529 sudo mvim /opt/local//var/db/postgresql90/defaultdb/pg_hba.conf 530 sudo su - postgres 531 sudo su - postgres 532 /opt/local/lib/postgresql90/bin/createdb 533 sudo su - postgres 534 /opt/local/lib/postgresql90/bin/createdb 535 echo $PATH 536 source .bashrc 537 echo $PATH 538 hi
user postgres History
$ sudo su - postgres Password: NIA-LG-01778617:~ postgres$ hi -bash: hi: command not found NIA-LG-01778617:~ postgres$ history 1 pwd 2 vim ./pg_start 3 vim ./pg_stop 4 chmod +x ./pg_stop 5 chmod +x ./pg_start 6 ./pg_start 7 cat ../../log/postgresql90/postgres.log 8 /opt/local/lib/postgresql90/bin/createuser colettace 9 exit 10 /opt/local/lib/postgresql90/bin/dropuser colettace 11 /opt/local/lib/postgresql90/bin/createuser chris 12 hi 13 history NIA-LG-01778617:~ postgres$ pwd /opt/local/var/db/postgresql90
Directions after Install
Postgres9.3
To create a database instance, after install do sudo mkdir -p /opt/local/var/db/postgresql93/defaultdb sudo chown postgres:postgres /opt/local/var/db/postgresql93/defaultdb sudo su postgres -c '/opt/local/lib/postgresql93/bin/initdb -D /opt/local/var/db/postgresql93/defaultdb'
Postgres10.4
Caviats from Homebrew
To migrate existing data from a previous major version of PostgreSQL run: brew postgresql-upgrade-database To have launchd start postgresql now and restart at login: brew services start postgresql Or, if you don't want/need a background service you can just run: pg_ctl -D /usr/local/var/postgres start
Create user
$createuser --interactive chris_psql_user Shall the new role be a superuser? (y/n) y Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y
Create DB
createdb lgfreezer
Change password
psql -d lgfreezer -h localhost -U chris_psql_user
lgfreezer=> alter user <username> with encrypted password '<password>';
Using in Python
import psycopg2 import sqlalchemy import pandas as pd conn_string = "host='localhost' dbname='lgfreezer' user='chris_psql_user2' password='sadkjhasfkljhdas'" conn = psycopg2.connect(conn_string) query.close() query = open('freezer_locations.sql') locations = pd.read_sql_query( query.read(), conn )
Turn server off
pg_ctl stop -D tempdb_dir/