Tag: setup

Postgres: PgAgent Installation

This describes the necessary steps to install PgAgent on Ubuntu 14.04.

PgAgent:

  • apt-get install pgagent
  • -u postgres psql
  • CREATE EXTENSION pgagent;
    • Check it exists by running select * from pg_extension;
  • Edit the .pgpass file and add the connections you will need.
    • If you don’t know how to add the .pgpass file see adding pgpass below.
  • Execute the following command
    • pgagent hostaddre=localhost dbname=postgres user=postgres
  • You can also follow https://www.pgadmin.org/docs/1.8/pgagent-install.html as well for installation.

.pgpass:

  • touch .pgpass
    • Put it in home directory.
    • It contains information on connections.
      • hostname:port:database:username:password
  • Modify permissions for owner read write only
    • chmod 600 .pgpass

Postgres: Setup

If you want to setup a database server for Postgresql 9.6 these are the basic steps that should be done when working with Ubuntu 16.04.

Installation:

  • sudo apt-get update
  • sudo apt-get upgrade
  • sudo reboot (if necessary)
  • sudo add-apt-repository “deb https://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main”
  • tee -a /etc/apt/sources.list.d/pgdg.list
  • wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
  • apt-get upgrade
  • sudo apt-get install postgresql-9.6
  • cd /var/
  • mkdir pg_log (This is where the logs will be stored)
  • chmod o+wxr /var/pg_log/
  • cd /etc/postgresql/9.6/main
  • nano postgresql.conf:
    • CONNECTIONS AND AUTHENTICATION
      • set “listen_addresses” = ‘*’
      • Set “max_connections” to 250 (or a reasonable number that works for your setup)
      • logging_collector = on
      • log_directory = ‘/var/pg_log’
      • log_file_mode = 0506
    • AUTOVACUUM PARAMETERS
      • autovacuum = on
        • Remove #
      • autocavuum_analyze_threshold = 100
      • autovacuum_vacuum_threshold = 100
      • track_counts = on
  • nano pg_hba.conf
    • host all all 0.0.0.0/0 md5
    • local all postgres trust
  • systemctl enable postgresql
  • systemctl start postgresql
  • /etc/init.d/postgresql restart
  • -u postgres psql template1
  • ALTER USER postgres with encrypted password ‘SET A PASSWORD’;
  • \q
  • -u postgres psql
  • CREATE EXTENSION adminpack;
    • To ensure it was installed correctly run “select * from pg_extension;”
  • If you want to view the postgresql service to find what directories are currently being using then run “ps auxw | grep postgres | grep — -D”

Create DB:

  • -u postgres psql
  • CREATE DATABASE ##DATABASE NAME##;
    • First run \l to check that DB doesn’t exist already.
  • CREATE ROLE ##ROLE NAME## LOGIN ENCRYPTED PASSWORD ‘##PASSWORD##’;
  • GRANT ##REQUIRED## PRIVILEGES ON DATABASE ##DATABASE NAME##TO ##ROLE NAME##;

Useful PSQL Commands

  • \l = list database
  • \q = quit
  • \c DBNAME = switch to db
  • \dt = list tables
  • ALTER DATABASE name OWNER TO new_owner
  • ALTER TABLE test OWNER TO test;