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 thepgc
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 5432Now 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:
C:\ cd PostgreSQL pgc info PGC: v3.3.5 C:\PostgreSQL User & Host: pguser \\pguser-PC\pguser-PC Os: Microsoft Windows 7 Ultimate, SP 1 Hardware: 0.5 GB, 1 x Intel Core i5-3210M @ 2.50GHz Last Update UTC: None