Postgres: Difference between revisions
From Leo's Notes
This page was last edited on 14 June 2020, at 23:53.
m →= Init DB: format |
m Added categories |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 16: | Line 16: | ||
## -W to prompt for a password | ## -W to prompt for a password | ||
## -h for hostname | ## -h for hostname | ||
## | ## -p for port | ||
## -d (optional) for the database name | |||
}} | }} | ||
Once in the psql console | 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 36: | Line 46: | ||
}} | }} | ||
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> | ||
| Line 43: | Line 58: | ||
</nowiki> | </nowiki> | ||
}} | }} | ||
|- | |||
| {{code|\dt}} | |||
| Describes tables of a database. | |||
{{highlight|lang=terminal|code= | {{highlight|lang=terminal|code= | ||
citrix-confdb=# \dt | citrix-confdb=# \dt | ||
| Line 55: | Line 71: | ||
(3 rows) | (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= | {{highlight|lang=terminal|code= | ||
<nowiki> | <nowiki> | ||
| Line 67: | Line 94: | ||
</nowiki> | </nowiki> | ||
}} | }} | ||
== Tasks == | |||
=== Process List === | |||
To show the process list like in MySQL, select from the {{code|pg_stat_activity}} table: | |||
{{highlight|lang=terminal|code= | |||
<nowiki> | |||
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 |
\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