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 (like Postgres or PostgreSQL Studio) by starting and stopping from the command line. PGC works across Linux, Windows, and OSX with the exact same command line interface and integrates with native OS controls for running services.

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 component2 ...]

      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
        enable     - Enable a server component
        disable    - Disable a server component from starting automatically
        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: v3.3.5  /Users/pguser/bigsql
          User & Host: pguser  machine-name 127.0.0.1
                   Os: Mac OS X 10.11.5
             Hardware: 16 GB, 2 x Intel Core i5-3210M @ 2.50GHz
      Last Update UTC: None


   $ ./pgc info pg95

              Project: pg (http://postgresql.org)
            Component: Component: pg95 9.5.3-2
         Release Date: 2016-05-12
                Stage: prod
                  Doc: http://www.postgresql.org/docs/9.5/
             RelNotes: http://www.postgresql.org/docs/9.5/static/release-9-5.html
            IsCurrent: 1
          IsInstalled: 1
                 port: 5432
              datadir: /Users/pguser/bigsql/data/pg95
               logdir: /Users/pguser/bigsql/data/logs/pg95
               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 pg95, is installed and enabled by default.

   $ ./pgc list
        Category     | Component      | Version    | Status         | Port     | Updates
        PostgreSQL     pg93                  9.3.13-5   NotInstalled
        PostgreSQL     pg94                  9.4.8-5    NotInstalled
        PostgreSQL     pg95                  9.5.3-5    Installed      5432
        Extensions     cassandra_fdw3-pg95   3.0.0-1    NotInstalled
        Extensions     hadoop_fdw2-pg95      2.1.0-1    NotInstalled
        Extensions     oracle_fdw1-pg95      1.4.0-1    NotInstalled
        Extensions     orafce3-pg95          3.3.0-1    NotInstalled
        Extensions     pljava15-pg95         1.5.0-1    NotInstalled
        Extensions     plv814-pg95           1.4.8-1    NotInstalled
        Extensions     postgis22-pg95        2.2.2-2    NotInstalled
        Servers        cassandra30           3.0.6      NotInstalled
        Applications   pgadmin3              1.22.1     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

       pg95 stopped on port 5432

The start command will start up postgres

   $ ./pgc start

       pg95 starting on port 5432

Running the status command displays information about installed server components.

   $ ./pgc status

       pg95 running on port 5432

Now lets use the stop command to shutdown the server components.

   $ ./pgc stop

       pg95 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 pg95
  Category   | Component          | Version  | Status
  Extensions   cassandra_fdw3-pg95   3.0.0-1   NotInstalled
  Extensions   hadoop_fdw2-pg95      2.1.0-1   NotInstalled
  Extensions   oracle_fdw1-pg95      1.4.0-1   NotInstalled
  Extensions   orafce3-pg95          3.3.0-1   NotInstalled
  Extensions   pljava15-pg95         1.5.0-1   NotInstalled
  Extensions   plv814-pg95           1.4.8-1   NotInstalled
  Extensions   postgis22-pg95        2.2.2-2   NotInstalled

And you use the install command to install extensions just like other components:

  ./pgc install orafce3-pg95
 ['orafce3-pg95']
Get:1 http://s3.amazonaws.com/pgcentral orafce3-pg95-3.3.0-1-osx64
 Unpacking orafce3-pg95-3.3.0-1-osx64.tar.bz2

After installing, enable the extension in the specific database you want to use it in via psql:


  postgres=# CREATE EXTENSION orafce;

5.) Using on Windows

When using PGC in a Windows command prompt, no ./ prefix like in the linux and OSX examples above. So a typical command would look like: