Tasks[edit]

Init DB[edit]

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

Client[edit]

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[edit]

Process List[edit]

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