Postgres

Tasks

Init DB

# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

Client

The command-line client for PosgreSQL is psql. Connect by running psql as root or as a particular user. Alternatively, specify the username, password, and host:

# psql -U ctxvda -W -h localhost citrix-confdb
## -U for username
## -W to prompt for a password
## -h for hostname
## -p for port
## -d (optional) for the database name

Once in the psql console, commands are issued with a backslash.

Common commands are:

Command	Description
\l	Lists databases postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------+----------+----------+-------------+-------------+----------------------- citrix-confdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) Alternatively, you can find all tables by selecting from pg_catalog.pg_tables.
\c	Change database postgres=# \c citrix-confdb You are now connected to database "citrix-confdb" as user "postgres".
\dt	Describes tables of a database. citrix-confdb=# \dt List of relations Schema
\q	Quits the console. Same with Ctrl+d
\!	Executes a system command. Eg. \! ls
\set	Sets parameters in psql. Some parameters are set in .psqlrc.

If the table name is in mixed case, the table name needs to be in quotes.

citrix-confdb=> SELECT * FROM "Value" ;
 ID  |                  Name                   | ValueDWORD |                          ValueSZ                          | ValueMultiSZ | ValueBinary | KeyID
-----+-----------------------------------------+------------+-----------------------------------------------------------+--------------+-------------+-------
   1 | Copyright                               |            | Copyright 2016 Citrix Systems, Inc.  All Rights Reserved. |              |             |    13
   2 | DotVersion                              |         15 |                                                           |              |             |    13
...

Tasks

Process List

To show the process list like in MySQL, select from the pg_stat_activity table:

postgres=#  select * FROM pg_stat_activity;
 datid | datname  |  pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |
        state_change          | waiting | state  |              query
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-
------------------------------+---------+--------+---------------------------------
 12923 | postgres | 22936 |       10 | postgres |                  | 127.0.0.1   | localhost       |       43833 | 2020-01-30 08:47:28.725184-06 |                               | 2020-01-30 10:34:50.51319-06  |
2020-01-30 10:34:50.513196-06 | f       | idle   | COMMIT