PostgreSQL

by Scott, Revised on June 8, 2018 (DatabasesOpen SourceWeb Development)

PostgreSQL

PostgreSQL apt repository - Also install libpq-dev for installing Perl module DBD::Pg

Drivers: DBI, DBD::Pg, Mojo::Pg

Data Types

Links to jsonb query help:

Database Managing

  1. Use the repo above.
  2. Create a system user (-r):

    sudo useradd -r username
    
  3. Change to postgres user:

    sudo su - postgres
    
  4. Create a PostgreSQL user (role) to match system user above (and interactive mode with password prompt):

    createuser --interactive -P username
    
  5. Create a DB owned by the new user:

    createdb -O username dbname
    
  6. Switch to new user account:

    sudo su - username
    
  7. Develop db from a sql file:

    psql dbname < dbname.sql
    
  8. Create backup and restore from superuser account (do not use -O switch for backup so the ownership of the tables to a non-superuser will be retained):

    pg_dump -c dbname > dbname.sql
    psql dbname < dbname.sql
    

I run the command in the last step numerous times during development. If I run it from a super user, it changes the ownership of the tables to the other user. If I do not create the system user, I need to enter the user role password a lot. Switch into the user acount allows me to update using peer authentication, so no password.

Helpful psql Client Commands

  • \q - quit
  • \l - list dbs
  • \du - list user/roles
  • \d - list relationships of db
  • \c dbname - connect to db
  • \password username - change password

Information

PostgreSQL automatically creates indexes on primary keys and unique constraints, but not on the referencing side of foreign key relationships.

Connect to LibreOffice Base

Install the following:

sudo apt-get install libreoffice-sdbc-postgresql

Tags: DatabasesOpen SourceWeb Development