Postgres: Difference between revisions

From Leo's Notes
This page was last edited on 14 June 2020, at 23:53.
← Older edit
VisualWikitext
mNo edit summary
m Added categories
 
(7 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Tasks ==
=== Init DB ===
{{highlight|lang=terminal|code=
# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK
}}


== Client ==
== Client ==
Line 8: Line 16:
## -W to prompt for a password
## -W to prompt for a password
## -h for hostname
## -h for hostname
## and the database name
## -p for port
## -d (optional) for the database name
}}
}}


Once in the psql console databases can be listed with {{code|\l}}:
Once in the psql console, commands are issued with a backslash.
 
Common commands are:
{|class="wikitable"
! Command
! Description
|-
| {{code|\l}}
| Lists databases
 
{{highlight|lang=terminal|code=
{{highlight|lang=terminal|code=
<nowiki>
<nowiki>
Line 28: Line 46:
}}
}}


A database can be selected or changed with {{code|\c}}.
Alternatively, you can find all tables by selecting from {{code|pg_catalog.pg_tables}}.
 
|-
| {{code|\c}}
| Change database
 
{{highlight|lang=terminal|code=
{{highlight|lang=terminal|code=
<nowiki>
<nowiki>
postgres=# \c citrix-confdb
postgres=# \c citrix-confdb
You are now connected to database "citrix-confdb" as user "postgres".
You are now connected to database "citrix-confdb" as user "postgres".
</nowiki>
}}
|-
| {{code|\dt}}
| Describes tables of a database.
{{highlight|lang=terminal|code=
citrix-confdb=# \dt
          List of relations
Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
reg    | Key        | table | postgres
reg    | Properties | table | postgres
reg    | Value      | table | postgres
(3 rows)
}}
|-
| {{code|\q}}
| Quits the console. Same with {{code|Ctrl+d}}
|-
| {{code|\!}}
| Executes a system command. Eg. {{code|\! ls}}
|-
| {{code|\set}}
| Sets parameters in psql. Some parameters are set in {{code|.psqlrc}}.
|}
If the table name is in mixed case, the table name needs to be in quotes.
{{highlight|lang=terminal|code=
<nowiki>
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
...
</nowiki>
</nowiki>
}}
}}


Tables can be listed with {{code|\dt}}:
== Tasks ==
=== Process List ===
To show the process list like in MySQL, select from the {{code|pg_stat_activity}} table:
 
{{highlight|lang=terminal|code=
{{highlight|lang=terminal|code=
citrix-confdb=# \dt
<nowiki>
No relations found.
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
</nowiki>
 
}}
}}
{{Navbox Linux}}[[Category:Linux]]
[[Category:LinuxUtilities]]

Latest revision as of 23:53, 14 June 2020

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=&gt; 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
Various Linux Notes
Linux Tools and Utilites
Package Management
Linux Distributions
Networking
Containers