PGCLI: Pretty Good Command Line Interface

PGC is a package and update manager, similar to yum or apt-get, specifically for PostgreSQL and related community projects. PGC enables you to easily find and install packages, configure multiple versions of PostgreSQL, update existing applications, and control services by starting and stopping from the command line.

1.) Review the help file

Get into the product home directory at the command line. Windows users don't prefix the pgc command with ./ as shown in the below examples.

Now run the help command to see that things are as expected

   $ ./pgc help

      Usage: pgc command [component1]

      Common commands:
        help       - Show this help file
        info       - Display OS or component information
        list       - Display installed & available components
        status     - Display status of installed server components
        start      - Start server components
        stop       - Stop server components
        tune       - Basic tuning of a postgresql.conf file
        update     - Retrieve new list of available components
        upgrade    - Upgrade installed components to newer (compatible) versions
        install    - Install a component
        remove     - Remove a component

      Advanced commands:
        init       - Initialize a component
        config     - Configure a component
        clean      - Remove component files from download cache
        enable     - Enable a server component
        disable    - Disable a server component from starting automatically

2.) Demonstrate the pgc command line environment

The info command lists information about the OS or a component
   $ ./pgc info

                  PGC: v4.2.2  /home/pguser/bigsql
          User & Host: pguser  machine-name
                   Os: Centos 7
             Hardware: 16 GB, 2 x Intel Core i5-3210M @ 2.50GHz
      Last Update UTC: None

   $ ./pgc info pg11

              Project: pg (
            Component: Component: pg11 11.5.3-2
         Release Date: 2016-05-12
                Stage: prod
            IsCurrent: 1
          IsInstalled: 1
                 port: 5432
              datadir: /home/pguser/bigsql/data/pg11
               logdir: /home/pguser/bigsql/data/logs/pg11
               status: Running
            data size: 7064 kB
             up since: 2 minutes and 53 seconds
          connections: 1/100

The list command displays installed and available components. It confirms that only the core
Postgres server component, in this case pg11, is installed and enabled by default.

   $ ./pgc list
        Category     | Component      | Version    | Status         | Port     | Updates
        PostgreSQL     pg11                  11.5-1     Installed      5432
        Extensions     cassandra_fdw3-pg11   3.0.0-1    NotInstalled
        Extensions     hadoop_fdw2-pg11      2.1.0-1    NotInstalled
        Extensions     oracle_fdw1-pg11      1.4.0-1    NotInstalled
        Extensions     orafce3-pg11          3.3.0-1    NotInstalled
        Extensions     pljava15-pg11         1.5.0-1    NotInstalled
        Extensions     plv814-pg11           1.4.8-1    NotInstalled
        Extensions     postgis22-pg11        2.2.2-2    NotInstalled
        Applications   pgbadger              8.1        NotInstalled

3.) Start and stop PostgreSQL

The status command confirms whether server components are listening on their assigned ports.
   $ ./pgc status

       pg11 stopped on port 5432

The start command will start up postgres
   $ ./pgc start

       pg11 starting on port 5432

Running the status command displays information about installed server components.
   $ ./pgc status

       pg11 running on port 5432
Now lets use the stop command to shutdown the server components.
   $ ./pgc stop

       pg11 stopping

4.) Installing PostgreSQL Extensions

Postgres by BigSQL includes additional useful extensions that can easily be installed to add additional functionality, procedural languages, or foreign data wrappers. Use the list command to see the extensions available for the Postgres versions installed:

  ./pgc list --extensions pg11
  Category   | Component          | Version  | Status
  Extensions   cassandra_fdw-pg11    3.0.0-1   NotInstalled
  Extensions   hadoop_fdw-pg11       2.1.0-1   NotInstalled
  Extensions   oracle_fdw-pg11       1.4.0-1   NotInstalled
  Extensions   orafce-pg11           3.3.0-1   NotInstalled
  Extensions   pljava-pg11           1.5.2-1   NotInstalled
  Extensions   plv814-pg11           1.4.8-1   NotInstalled
  Extensions   postgis25-pg11        2.5.2-1   NotInstalled

The following install command uses the optional '-d' command to install orface in the 'demo' database:

  ./pgc install orafce-pg11 -d demo
Get:1 orafce3-pg11-3.3.0-1-linux64
 Unpacking orafce3-pg11-3.3.0-1-linux64.tar.bz2
  postgres=# CREATE EXTENSION orafce;