215 lines
5.3 KiB
ReStructuredText
215 lines
5.3 KiB
ReStructuredText
|
|
**********************
|
|
PostgreSQL
|
|
**********************
|
|
|
|
.. contents:: Table of Contents
|
|
|
|
Repository Manager
|
|
==================
|
|
|
|
The Repository installer and manager allows you to install your selected version of the PostgreSQL Repository.
|
|
|
|
It can be access via the Pg Installer tab as shown below:
|
|
|
|
.. image:: _static/postgresql-tab.png
|
|
|
|
You can also use the manager to install and update packages as well.
|
|
|
|
As certain packages require EPEL for CentOS, the EPEL repository is installed as well when installing on CentOS.
|
|
|
|
.. image:: _static/pgsql-installer.png
|
|
|
|
File Locations
|
|
==============
|
|
|
|
The PostgreSQL config direcotry is located at::
|
|
|
|
/etc/postgresql/15/main
|
|
|
|
|
|
The pg_hba.conf File
|
|
====================
|
|
|
|
On installation via the Wizard, PostgreSQL is configured for use with SSL and uses scram-sha-256 authentication for all users and databases.
|
|
|
|
.. code-block:: bash
|
|
|
|
|
|
local all all trust
|
|
host all all 127.0.0.1 255.255.255.255 trust
|
|
host all all 0.0.0.0/0 scram-sha-256
|
|
host all all ::1/128 scram-sha-256
|
|
hostssl all all 127.0.0.1 255.255.255.255 scram-sha-256
|
|
hostssl all all 0.0.0.0/0 scram-sha-256
|
|
hostssl all all ::1/128 scram-sha-256
|
|
|
|
|
|
|
|
|
|
The postgresql.conf File
|
|
========================
|
|
|
|
On installation via the Wizard, PostgreSQL is configured to accept connections on all interfaces as well as SSL connections.
|
|
|
|
.. code-block:: bash
|
|
|
|
|
|
#------------------------------------------------------------------------------
|
|
# CONNECTIONS AND AUTHENTICATION
|
|
#------------------------------------------------------------------------------
|
|
|
|
# - Connection Settings -
|
|
|
|
listen_addresses = '*'
|
|
)
|
|
|
|
|
|
# - SSL -
|
|
|
|
ssl = on
|
|
|
|
Above are excepts.
|
|
|
|
On installation, the native PostgreSQL Database Server module is also activated.
|
|
|
|
It is located under Servers > PostgreSQL Database Server
|
|
|
|
.. image:: _static/PostgreSQL-Webmin.png
|
|
|
|
|
|
|
|
.. This is a comment. Note how any initial comments are moved by
|
|
transforms to after the document title, subtitle, and docinfo.
|
|
|
|
.. demo.rst from: http://docutils.sourceforge.net/docs/user/rst/demo.txt
|
|
|
|
.. |EXAMPLE| image:: static/yi_jing_01_chien.jpg
|
|
:width: 1em
|
|
|
|
Basic Commands
|
|
**********************
|
|
|
|
Below are some basic PSQL commands.
|
|
|
|
PostgreSQL documentation, both from PostgreSQL.org and other sources, is extensive.
|
|
|
|
https://www.postgresql.org/docs/current/tutorial.html
|
|
|
|
https://www.postgresqltutorial.com/
|
|
|
|
Connecting to PostgreSQL
|
|
-------------------------
|
|
|
|
There are a number of options for viewing your PostgreSQL data.
|
|
|
|
1. PSQL command line
|
|
2. PgAdmin
|
|
3. Control Panel
|
|
|
|
|
|
PSQL
|
|
---------------
|
|
|
|
Connect via SSH and su to postgres
|
|
|
|
.. code-block:: console
|
|
|
|
root@demo:# su - postgres
|
|
|
|
Start psql
|
|
|
|
.. code-block:: console
|
|
|
|
postgres@demo:~$ psql
|
|
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
|
|
Type "help" for help.
|
|
|
|
List all databases using the '\\l' command
|
|
|
|
.. code-block:: console
|
|
|
|
postgres=# \l
|
|
List of databases
|
|
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
|
|
------------+----------+----------+---------+---------+------------+-----------------+-----------------------
|
|
geostore | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
|
|
geodb | geouser | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
|
|
postgisftw | pgis | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
|
|
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
|
|
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
|
|
| | | | | | | postgres=CTc/postgres
|
|
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
|
|
| | | | | | | postgres=CTc/postgres
|
|
(5 rows)
|
|
|
|
|
|
Connect to target database (in this case, postgisftw) using the '\\c' command
|
|
|
|
.. code-block:: console
|
|
|
|
postgres=# \c postgisftw
|
|
You are now connected to database "postgisftw" as user "postgres".
|
|
|
|
List tables in database using the '\\dt' command
|
|
|
|
.. code-block:: console
|
|
|
|
postgisftw=# \dt
|
|
List of relations
|
|
Schema | Name | Type | Owner
|
|
--------+-------------------+-------+----------
|
|
public | configuration | table | pgis
|
|
public | countries | table | pgis
|
|
public | pointsofinterest | table | pgis
|
|
public | spatial_ref_sys | table | postgres
|
|
public | ways | table | pgis
|
|
public | ways_vertices_pgr | table | pgis
|
|
(6 rows)
|
|
|
|
|
|
Select country name from the countries table, limited to 10:
|
|
|
|
.. code-block:: console
|
|
|
|
postgisftw=# select name from countries limit 10;
|
|
name
|
|
--------------------------
|
|
Zimbabwe
|
|
Zambia
|
|
Montserrat
|
|
Yemen
|
|
Vanuatu
|
|
Uruguay
|
|
Vietnam
|
|
Micronesia
|
|
Venezuela
|
|
Vatican
|
|
(10 rows)
|
|
|
|
|
|
Select country name from the countries table, limited to 10, sorting acecnded:
|
|
|
|
.. code-block:: console
|
|
|
|
postgisftw=# select name from countries order by name ASC limit 10;
|
|
name
|
|
-------------------
|
|
Afghanistan
|
|
Albania
|
|
Algeria
|
|
American Samoa
|
|
Andorra
|
|
Angola
|
|
Anguilla
|
|
Antarctica
|
|
Antigua and Barb.
|
|
Argentina
|
|
(10 rows)
|
|
|
|
postgisftw=#
|
|
|
|
|
|
|
|
|