Connecting to PostgreSQL

Once you've installed, the question is usally, "How do I connect to Postgres?" On Windows, BigSQL includes 3 ways to connect interactively - the default command line (psql), the desktop app pgAdmin III, and the web based PostgreSQL Studio.

Using psql

The most frequent way of accessing Postgres is using the psql command line interactive shell. Psql is always installed with Postgres by BigSQL and makes it easy to develop and maintain databases.

On Windows, a short-cut to psql is added to the Start Menu so you can quickly open a psql window.

Clicking the psql app will open a new command window with psql connected to the initial PostgreSQL database.

From a command window

If you want to launch psql from the command line, we provide an environment file that makes it easy to set your PG environment variables. Run this file and run psql.


\> cd C:\Program Files\PostgreSQL\pg95
\> pg95-env.bat
\> psql

You can tell you're connected to the recently installed version:


psql (9.5.1)
Type "help" for help.

postgres=#

Using pgAdmin III

pgAdmin is a popular graphical desktop app available on OS X and Windows that supports all Postgres development and administration tasks. PG Admin can be optionally installed when running the PostgreSQL installer for OSX or added using the PGC update manager.

1. Open Launchpad or the PostgreSQL folder under Applications. Click the icon for pgAdmin III

2. Click the plug icon to create a new server connection.

3. Create a name for this server connection. Enter "localhost" as the host name. Enter the port that you specified during your install, by default this is 5432. Enter the name of the superuser "postgres" and the password you created during installation. You will first connect to the default database, also named "postgres". Click "ok".

4. Expand headings to view the contents of the object browser. Here you will be able to preview all of the objects in your database.

5. Click on the magnifying glass icon to execute SQL queries.

6. Type in: SELECT * FROM information_schema.tables;

To see the names of the tables that exist in your database. Click the play button to execute query.

7. The results of this query will show in the Data Output tab of the Output Pane.

Using PostgreSQL Studio

PostgreSQL Studio is a powerful web based development and management tool that makes it easy to edit your Postgres database and functions from any web browser. PG Studio is ideal if you are running your database in a cloud or remote data center and want secure access from any location without directly exposing your database to the internet.

PostgreSQL Studio can be optionally installed when running the PostgreSQL installer for OSX or added using PGC update manager.

1. Open Launchpad or the PostgreSQL folder under Applications. Click the icon for PostgreSQL Studio. Or, open a browser and navigate to http://localhost:8765.

2. To connect to your Postgres database, enter the localhost as the host name. Enter the port that was used during installation, by default this is 5432. Enter the username (postgres) and password credentials.

3. When you click the "refresh" button PostgreSQL Studio will show you all of the local databases you can connect to. Choose the appropriate database and click login.

4. PostgreSQL Studio is ready to be used.