Postgresql

From Colettapedia
Jump to navigation Jump to search

Reference

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

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
  • 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
  1. Start the server: systemctl enable --now postgresql-12
    • Check status: systemctl status postgresql-12
3. Change superuser password
  1. Change UNIX user to postgres and change SuperUser password
    1. su - postgres
    2. psql -c "alter user postgres with password 'StrongPassword'"

pgadmin4 setup

$ 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/

Retrieved from "https://chriscoletta.com/index.php?title=Postgresql&oldid=4215"