MANAGEMENT: Query Postgres database externally and internally.

[yalegria]

Once Postgres database is set and running, we should verify accessibility by trying to query the database within the VM and/or container as well as externally.

[Voxelghiest]

PostgreSQL Database Management

Hubs uses PostgreSQL for its database management system (hereafter referred to as PQSL). PSQL has a command line interface, which can be used to query information about the database, its contents, etc. A few common useful commands are listed below (taken from this website):

Accessing the PSQL Shell

In PSQL, databases are associated with a user. Hubs uses the default user, postgres, for its database. To access the database, you must first switch to the postgres user in your normal shell, then run the command to open the PSQL shell:

username@server: $ sudo -i -u postgres
postgres@server: $ psql

All of the following commands are within the context of the PSQL shell, not the normal Bash shell. PSQL has its own unique syntax and set of commands. To exit the PSQL shell, type \q, then exit if you wish to return to your normal user.

Listing Available Databases

postgres=# \l

This lists all databases associated with the current user. For example:

Adding a + onto the end of the command will show additional details. When you are done scrolling through the list, pressing q will return control to the PSQL shell. These hold true for many other commands.

Opening a Database

postgres=# \c <database_name>

The contents of a database can only be examined by opening it up first. For example, Hubs uses the database ret_dev for its storage:

Once inside a database, further commands can act on it.

Listing the Contents of a Database

While inside a database:

database_name=# \dt

This lists all tables within that database. For example:

Adding a + onto the end of the command will show additional details. When you are done scrolling through the list, pressing q will return control to the PSQL shell.

As with many tickets within this project, this is a work-in-progress. Many more useful commands can be found on this tutorial website or within the PSQL documentation.

[yalegria]

Check access to the server externally.