How To Install and Use PostgreSQL on Ubuntu 18.04

Introduction

Introduction

Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It’s standards-compliant and has many advanced features like reliable transactions and concurrency without read locks.

Introduction

Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

Описание

Поскольку у нас нет каких-либо особых потребностей, мы выбираем более простой метод — установку двоичного пакета установки. Метод установки двоичного пакета обычно выполняется через диспетчер пакетов в разных дистрибутивах Linux, таких как Debian и Ubuntu. Apt-get команда или команда aptitude для установки, команда выглядит следующим образом

 apt-get install postgresql

И в RedHat. В CentOS или Fedora используйте инструмент yum для установки PostgreSQL, команда выглядит следующим образом:

yum install postgresql-.x86_64

Что такое PostgreSQL простыми словами.

Создание пользователя
Использование групп
Редактирование пользователя
Удаление пользователя или группы
Особые права
Для резервного копирования
Графический интерфейс

su — postgres

* если система выдаст ошибку, связанную с нехваткой прав, сначала повышаем привилегии командой sudo su или su.

Теперь запускаем командную оболочку PostgreSQL:

$ psql -Upostgres template1

* в данном примере, вход выполняется от учетной записи postgres к шаблонной базе template1.

Для просмотра всех пользователей СУБД:

1 Настройка пользователей и групп

Используйте настройки библиотеки yum для установки версии postgresql94, лучше всего сначала создать группы пользователей и пользователей:
(1) Добавьте группу пользователей postgres:

groupadd postgres

(2) Добавьте пользователя postgres и установите его принадлежность к группе пользователей postgres, созданной выше:

useradd  postgres postgres

(3) Измените пароль пользователя postgres: passwd postgres (здесь установите пароль на postgres)

   Измените пароль пользователя postgres

[root software]
[root software]# groupadd postgres
[root software]
[root software]# passwd postgres
Changing password  user postgres.
New : 
BAD : it  based  a dictionary word
BAD :  too simple
Retype  : 
: all authentication tokens updated successfully.
[root software]

Посмотреть версию системы linux

Ссылка: 03-centos Как узнать, какая версия операционной системы:http://blog.csdn.net/qq_21383435/article/details/79444648
Мой выглядит следующим образом

[[email protected] ~]# cat /proc/version   
Linux version -el686_64 ([email protected]) (gcc version   (Red Hat -) (GCC) ) #1 SMP Fri Nov 22 03:15:09 UTC 2013
[[email protected] ~]#  cat /etc/issue  
CentOS release  (Final)
Kernel \r on an \m

[[email protected] ~]# getconf LONG_BIT  

версия centos6.5, система 64 бит
Итак, я выбираю следующее

Вы можете видеть, что после выбора официального сайта есть способ установки, но я не устанавливаю его таким образом, потому что мне нравится устанавливать его сам, я знаю, где установить, и справляюсь с общей ситуацией

Step 6 — Creating and Deleting Tables

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

First, create a table to store some data. As an example, a table that describes some playground equipment.

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

For demonstration purposes, create a sample table like this:

CREATE TABLE playground 
    equip_id serial PRIMARY KEY,
     varchar  NOT NULL,
    color varchar  NOT NULL,
    location varchar check location  , , , , , , , ,
    install_date 

These commands will create a table that inventories playground equipment. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of primary key which means that the values must be unique and not null.

For two of the columns (equip_id and install_date), the commands do not specify a field length. This is because some column types don’t require a set length because the length is implied by the type.

The next two commands create columns for the equipment type and color respectively, each of which cannot be empty (as specified by the NOT NULL constraint applied to each). The line after these creates a location column and adds a constraint requiring this column’s values to be one of eight possible values. The last line within the parentheses creates a date column that records the date on which you installed the equipment.

Note that in SQL, every statement must end in a semicolon (;).

If you entered the CREATE TABLE operation correctly, it will return this output:

CREATE TABLE

You can find a list of tables within this database by typing:

                  List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type which you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.

If you only want the table returned without the sequence, you can type:

dt

          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Column

Available column types

Create Enum type

CREATE TYPE environment AS ENUM ('development', 'staging', 'production');

Add column to table

ALTER TABLE [table_name] ADD COLUMN [column_name] [data_type];

Remove column from table

ALTER TABLE [table_name] DROP COLUMN [column_name];

Change column data type

ALTER TABLE [table_name] ALTER COLUMN [column_name] [data_type];

Change column name

ALTER TABLE [table_name] RENAME COLUMN [column_name] TO [new_column_name];

Set default value for existing column

ALTER TABLE [table_name] ALTER_COLUMN created_at SET DEFAULT now();

Add UNIQUE constrain to existing column

ALTER TABLE [table_name] ADD CONSTRAINT [constraint_name] UNIQUE ([column_name]);

or shorter (PostgreSQL will automatically assign a constrain name)

ALTER TABLE [table_name] ADD UNIQUE ([column_name]);

User

Create role

On the command line

sudo -u postgres createuser [role_name] -d -P

CREATE ROLE [role_name] WITH LOGIN CREATEDB PASSWORD '[password]';

List roles

\du

select * from pg_roles

Change a user’s password

ALTER ROLE [role_name] WITH PASSWORD '[new_password]';

Allow user to create databases

ALTER USER <username> WITH CREATEDB;

PSQL

Render NULL visible in psql

By default NULL is indifferentiable from an empty string.

\pset null ¤

Extended display mode

Extended display on and off depending on the size

\x auto

Using Group Roles in PostgreSQL

First create the new role that will be used as a group:

CREATE ROLE temporary_users

GRANT temporary_users TO demo_role

GRANT temporary_users TO test_user

You can view the role membership information by typing:

du

                                          List of roles
    Role name    |                         Attributes                         |     Member of     
-----------------+------------------------------------------------------------+-------------------
 demo_role       |                                                            | {temporary_users}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 temporary_users | Cannot login                                               | {}
 test_user       |                                                            | {temporary_users}

SET ROLE temporary_users

CREATE TABLE hello 
name varchar,
 serial,
start_date 

Now, check the table ownership by issuing this command:

                 List of relations
 Schema |     Name     |   Type   |      Owner      
--------+--------------+----------+-----------------
 public | demo         | table    | postgres
 public | demo_id_seq  | sequence | postgres
 public | hello        | table    | temporary_users
 public | hello_id_seq | sequence | temporary_users
(4 rows)

RESET ROLE

ALTER ROLE test_user INHERIT

DROP ROLE temporary_users

ERROR:  role "temporary_users" cannot be dropped because some objects depend on it
DETAIL:  owner of sequence hello_id_seq
owner of table hello

ALTER TABLE hello OWNER TO demo_role

              List of relations
 Schema |     Name     |   Type   |   Owner   
--------+--------------+----------+-----------
 public | demo         | table    | postgres
 public | demo_id_seq  | sequence | postgres
 public | hello        | table    | demo_role
 public | hello_id_seq | sequence | demo_role
(4 rows)

DROP ROLE temporary_users

Step 8 — Adding and Deleting Columns from a Table

After creating a table, you can modify it to add or remove columns relatively easily. Add a column to show the last maintenance visit for each piece of equipment by typing:

ALTER TABLE playground ADD last_maint 

The next time you view your table information again, the new column will have been added (but no data will have been entered):

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

To delete a column, you can enter an SQL statement very similar to the one you used to add the last_maint column. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete of the column by typing:

ALTER TABLE playground DROP last_maint

This deletes the last_maint column and any values found within it, but leaves all the other data intact.

JSON

SELECT '{"arr":[2,4,6,8]}'::json -> 'arr' -> 2              # returns 6

SELECT '{"arr":[2,4,6,8]}'::json #> ARRAY['arr','2']        # returns 6

Create JSON array

CREATE TABLE test (
  j JSON,
  ja JSON[]
);

INSERT INTO test(ja) VALUES (
  array[
    '{"name":"alex", "age":20}'::json,
    '{"name":"peter", "age":24}'::json
  ]
);

7 Установить автоматический запуск

chkconfig —list просмотреть все службы и найти имя службы postgre

[root postgresql]# chkconfig --list
postgresql-                     

По умолчанию postgresql-10 не запускается автоматически, нам нужно установить службу postgresql-10 как самозапускающуюся службу:

chkconfig postgresql- 
chkconfig 

[root postgresql]# chkconfig --list
postgresql-                         

9 Используйте клиентский инструмент Navicat для PostgreSQL для подключения

Granting Permissions in PostgreSQL

You can grant permissions using the GRANT command with this general syntax:

GRANT permission_type ON table_name TO role_name

CREATE TABLE demo 
name varchar,
 serial,
start_date 

To view the table you created, enter this command:

             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | demo        | table    | postgres
 public | demo_id_seq | sequence | postgres
(2 rows)

Notice that there is one table type and one sequence type. The sequence is generated for you when you used the id serial command in your table creation. This generates an auto-incrementing integer.

GRANT UPDATE ON demo TO demo_role

GRANT ALL ON demo TO test_user

GRANT INSERT ON demo TO PUBLIC

                                      Access privileges
 Schema |    Name     |   Type   |     Access privileges      | Column privileges | Policies 
--------+-------------+----------+----------------------------+-------------------+----------
 public | demo        | table    | postgres=arwdDxt/postgres +|                   | 
        |             |          | demo_role=w/postgres      +|                   | 
        |             |          | test_user=arwdDxt/postgres+|                   | 
        |             |          | =a/postgres                |                   | 
 public | demo_id_seq | sequence |                            |                   | 
(2 rows)

This reveals all the grant permissions that have been assigned.

Database

List all databases

\l

Create database

on the command line

sudo -u postgres createdb [name] -O [role_name]

CREATE DATABASE [name] OWNER [role_name];

Drop database

on the command line

sudo -u postgres dropdb [name] --if-exists

DROP DATABASE IF EXISTS [name];

Export database as CSV

COPY (SELECT * FROM widgets) TO '/absolute/path/to/export.csv'
WITH FORMAT csv, HEADER true;

COPY requires to specify an absolute path and only writes to the file system
local to the database. \copy is a wrapper around COPY that circumvents those
restrictions.

\copy (SELECT * FROM widgets) TO export.csv
WITH FORMAT csv, HEADER true

You can specify encoding of exported data (e.g. latin1 for Excel instead of
utf-8).

\copy (SELECT * FROM widgets) TO export.csv
WITH FORMAT csv, HEADER true, ENCODING 'latin1'

Create compressed PostgreSQL database backup

pg_dump -U [role_name] [db_name] -Fc > backup.dump

Create schema-only database backup

Schema-only means tables, indexes, triggers, etc, but not data inside; done -s
option.

pg_dump -U [role_name] [db_name] -s > schema.sql

Restore database from binary dump

PGPASSWORD=<password> pg_restore -Fc --no-acl --no-owner -U <user> -d <database> <filename.dump>

Create compressed backups for all databases at once

pg_dump -Fc

Convert binary database dump to SQL file

pg_restore binary_file.backup > sql_file.sql

Copy database quickly

In order to copy a database, we create a new database and specify an existing
one as the template.

createdb -T app_db app_db_backup

Change database ownership

ALTER DATABASE acme OWNER TO zaiste;

2 Установка официального сайта Executive

Установите RPM репозитория: установите RPM репозитория

yum install /-centos1--.noarch.rpm

Установите клиентские пакеты: установите клиентские пакеты.

yum  postgresql10

При необходимости установите пакеты сервера: При желании установите пакеты сервера

yum install postgresql10-

При желании инициализировать базу данных и включить автоматический запуск: вы можете инициализировать базу данных и включить автоматический запуск.

service postgresql- initdb
chkconfig postgresql- 
service postgresql- start

Моя выполнила первые три команды

Step 5 — Opening a Postgres Prompt with the New Role

 adduser sammy

Once this new account is available, you can either switch over and connect to the database by typing:

   sammy
psql

Or, you can do this inline:

  sammy psql

This command will log you in automatically, assuming that all of the components have been properly configured.

psql  postgres

Once logged in, you can get check your current connection information by typing:

conninfo

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Step 3 — Creating a New Role

createuser 

If, instead, you prefer to use sudo for each command without switching from your normal account, type:

  postgres createuser 

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

You can get more control by passing some additional flags. Check out the options by looking at the man page:

 createuser

Step 5 — Opening a Postgres Prompt with the New Role

 adduser sammy

Once this new account is available, you can either switch over and connect to the database by typing:

   sammy
psql

Or, you can do this inline:

  sammy psql

This command will log you in automatically, assuming that all of the components have been properly configured.

psql  postgres

Once logged in, you can get check your current connection information by typing:

conninfo

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Installation and configuration

Install PostgreSQL on Debian/Ubuntu

Add APT repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib postgresql-client libpq-dev

Install PostgreSQL on macOS

brew install postgresql

Set password for postgres (PostgreSQL root) role

sudo -u postgres psql
\password

Disable PostgreSQL version

sudo service postgresql status
9.1/main (port 5432): online
9.2/main (port 5433): online
9.4/main (port 5434): online

Each PostgreSQL cluster in Debian/Ubuntu has a start.conf file that controls
what /etc/init.d/postgresql should do. Replace auto with manual in
/etc/postgresql/9.x/main/start.conf.

Disable auto boot

update-rc.d -f postgresql remove

Show clusters

sudo pg_lsclusters

Upgrade PostgreSQL

Install the newest PostgreSQL version. Check if you have more than one cluster running.

sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Stop the newer cluster

sudo pg_dropcluster 10 main --stop

Upgrade the older cluster to the newset versio

sudo pg_upgradecluster 9.6 main

Remove the older cluster

sudo pg_dropcluster 9.6 main

Changing Privileges of Roles in PostgreSQL

To change the attributes of an already created role, use the ALTER ROLE command. The syntax for this command is:

ALTER ROLE role_name WITH attribute_options

ALTER ROLE demo_role WITH NOLOGIN

You can confirm the change with the \du command:

du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}

 ALTER ROLE demo_role WITH LOGIN

Now the role has been reverted.

Step 4 — Creating a New Database

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

If you are logged in as the postgres account, you would type something like:

createdb sammy

If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:

  postgres createdb sammy

This flexibility provides multiple paths for creating databases as needed.

Step 2 — Using PostgreSQL Roles and Databases

There are a few ways to use this account to access Postgres.

Switching Over to the postgres Account

Switch over to the postgres account on your server by typing:

   postgres

You can now access a Postgres prompt immediately by typing:

psql

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

Exit out of the PostgreSQL prompt by typing:

This will bring you back to the postgres Linux command prompt.

Accessing a Postgres Prompt Without Switching Accounts

  postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

Again, you can exit the interactive Postgres session by typing:

Many use cases require more than one Postgres role. Read on to learn how to configure these.

Назначение особых прав пользователям PostgreSQL

Помимо ALL PRIVILEGES можно выдавать права на особые операции, например:

=# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO «dmosk»;

* команда позволит выдать права на получение данных, их обновление и добавление. Другие операции, например, удаление будут запрещены для пользователя dmosk.

Назначение прав для определенной таблицы:

5 Конфигурация

Создайте каталог для хранения файлов базы данных, каталогов журналов и измените разрешения

[[email protected] opt]# mkdir -p /opt/moudles/postgresql
[[email protected] opt]# mkdir -p /opt/moudles/postgresql/data
[[email protected] opt]# mkdir -p /opt/moudles/postgresql/log
[[email protected] opt]# chown -R postgres:postgres postgresql
chown: cannot access : No such file or directory
[[email protected] opt]# cd moudles/
[[email protected] moudles]# chown -R postgres:postgres postgresql
[[email protected] moudles]# 

Измените PGDATA и PGLOG в файле конфигурации базы данных postgresql (/etc/rc.d/init.d/postgresql-10) в каталог, созданный нами самими.

=
=

После сохранения продолжайте запрашивать место хранения файла initdb:

find / -  initdb найдите место установки initdb

[root postgresql]# find / -name initdb
/usr/pgsql-/bin/initdb
[root postgresql]

Укажите место хранения базы данных и метод кодирования и инициализируйте базу данных:

/usr/pgsql/bin/initdb  UNICODE  /opt/moudles/postgresql/

[[email protected] postgresql]# /usr/pgsql-10/bin/initdb -E UNICODE -D /opt/moudles/postgresql/data
initdb: cannot be   root
Please   (using, e.g., )   (unprivileged) user  will
own  server process.
[[email protected] postgresql]

Примечание. Приведенная выше ошибка указывает на то, что Postgresql не может быть инициализирован как root и должен быть запущен как пользователь postgres, поэтому пользователь linux postgres и группа postgres должны быть созданы выше.

Переключить пользователя postgres для выполнения начальной операции с базой данных

[[email protected] postgresql]# su postgres
[[email protected] postgresql]$ /usr/pgsql-/bin/initdb -E UNICODE -D /opt/moudles/postgresql/data
The files belonging to this database system will be owned by user .
This user must also own the server process.

The database cluster will be initialized with locale .
The default text search configuration will be set to .

Data page checksums are disabled.

fixing permissions on existing directory /opt/moudles/postgresql/data  ok
creating subdirectories  ok
selecting default max_connections  
selecting default shared_buffers  128MB
selecting dynamic shared memory implementation  posix
creating configuration files  ok
running bootstrap script  ok
performing post-bootstrap initialization  ok
syncing data to disk  ok

WARNING: enabling  authentication  local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the  time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-/bin/pg_ctl -D /opt/moudles/postgresql/data -l logfile start

[[email protected] postgresql]$ 

Запросить содержимое каталога после успешной инициализации базы данных

[[email protected] postgresql]$ ll /opt/moudles/postgresql/
 
------ 5 postgres postgres  4096 Mar  5 14:31 base
------ 2 postgres postgres  4096 Mar  5 14:31 global
------ 2 postgres postgres  4096 Mar  5 14:31 pg_commit_ts
------ 2 postgres postgres  4096 Mar  5 14:31 pg_dynshmem
-rw------- 1 postgres postgres  4513 Mar  5 14:31 pg_hba.conf
-rw------- 1 postgres postgres  1636 Mar  5 14:31 pg_ident.conf
------ 4 postgres postgres  4096 Mar  5 14:31 pg_logical
------ 4 postgres postgres  4096 Mar  5 14:31 pg_multixact
------ 2 postgres postgres  4096 Mar  5 14:31 pg_notify
------ 2 postgres postgres  4096 Mar  5 14:31 pg_replslot
------ 2 postgres postgres  4096 Mar  5 14:31 pg_serial
------ 2 postgres postgres  4096 Mar  5 14:31 pg_snapshots
------ 2 postgres postgres  4096 Mar  5 14:31 pg_stat
------ 2 postgres postgres  4096 Mar  5 14:31 pg_stat_tmp
------ 2 postgres postgres  4096 Mar  5 14:31 pg_subtrans
------ 2 postgres postgres  4096 Mar  5 14:31 pg_tblspc
------ 2 postgres postgres  4096 Mar  5 14:31 pg_twophase
-rw------- 1 postgres postgres     3 Mar  5 14:31 PG_VERSION
------ 3 postgres postgres  4096 Mar  5 14:31 pg_wal
------ 2 postgres postgres  4096 Mar  5 14:31 pg_xact
-rw------- 1 postgres postgres    88 Mar  5 14:31 postgresql.auto.conf
-rw------- 1 postgres postgres 

Отредактируйте файл postgresql.conf и измените адрес прослушивания и параметры порта, которые база данных получает по умолчанию:

cat postgresql | grep -n listen_addresses найти listen_addresses в postgresqlПоложение в файле и номер строки отображения
vi postgresql  Редактировать postgresqlфайл

[postgres postgresql]cd  /opt/moudles/postgresql/data
[postgres data]cat postgresql.conf | grep -n listen_addresses
#listen_addresses = 'localhost'              # what IP address(es) to listen on;
[postgres data]

Измените значение listen_addresses:

listen_addresses = 
 превратиться в
listen_addresses = 

Затем продолжайте изменять файл pg_hba.conf, чтобы сообщить серверу базы данных, каким типам клиентов он позволит подключаться к себе:

  Измените файл подключения службы postgresql

Добавьте строку под строкой 86, чтобы любой клиент мог получить доступ к себе с правильным именем пользователя и паролем:

host                              /               trust

# IPv4 local connections:
host                              /            trust
host                              /               trust

Примечания: Чтобы установить доверие, вы можете использовать psql -U postgres для прямого входа на сервер; чтобы установить одноранговый узел, вы можете использовать psql -h 127.0.0.1 -d postgres -U postgres для прямого входа на сервер;

8 Проверить статус

service postgresql-9.4 status Просмотр статуса службы

[root postgresql]# service postgresql-10 status
postgresql- (pid  )  running...
[root postgresql]

Step 4 — Creating a New Database

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

If you are logged in as the postgres account, you would type something like:

createdb sammy

If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:

  postgres createdb sammy

This flexibility provides multiple paths for creating databases as needed.

Удаление пользователей и групп

Удаление пользователя выполняется следующей командой:

database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM «dmosk»;

* обратите внимание, данный запрос отличается от предоставления прав двумя моментами: 1) вместо GRANT пишем REVOKE; 2) вместо TO «dmosk» пишем FROM «dmosk»;

Учетная запись для резервного копирования

Для выполнения резервного копирования лучше всего подключаться к базе с минимальными привилегиями. 

Сначала создаем роль, которую будем использовать для выполнения резервного копирования:

Предоставляем права на подключения к базе

* в данном примере к базе database.

Подключаемся к базе (в нашем примере database):

Даем права на все последовательности в схеме:

* мы дали права для схемы public. Это схема является схемой по умолчанию, но в вашем случае она может быть другой. В таком случае, подставляем свое значение.

6 Пуск

Обратите внимание: для запуска необходимо использовать пользователя root

[postgres data]service postgresql- start
 postgresql-   cannot set    permitted
[]
[postgres data]exit
exit
[root postgresql]# service postgresql-10 start
 postgresql-  [    ]
[root postgresql]

Table

List tables

\dt

Create table

CREATE TABLE widgets (
  id BIGINT PRIMARY KEY,
  name VARCHAR(20),
  price INT,
  created_at timestamp without time zone default now()
)

Insert into table

INSERT INTO widgets VALUES(1,'widget1',100)
INSERT INTO widgets(name, price) VALUES ('widget2', 101)

Upsert into table

PostgreSQL 9.5 or newer:

INSERT INTO widgets VALUES ... ON CONFLICT UPDATE

PostgreSQL 9.4 (and older) doesn’t have built-in UPSERT (or MERGE) facility and it’s difficult to do it right (concurrent use).

Drop table

DROP TABLE IF EXISTS widgets

Delete all rows from table

DELETE FROM widgets;

Drop table and dependencies

DROP TABLE table_name CASCADE;

Step 8 — Adding and Deleting Columns from a Table

After creating a table, you can modify it by adding or removing columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

ALTER TABLE playground ADD last_maint 

If you view your table information again, you will see the new column has been added but no data has been entered:

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)

If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete of the column by typing:

ALTER TABLE playground DROP last_maint

This deletes the last_maint column and any values found within it, but leaves all the other data intact.

Viewing Roles and Permissions in PostgreSQL

First, make sure your server is running by using the systemctl start command:

 systemctl start postgresql.service

Then, you can switch to the postgres account by typing:

   postgres

You can now access the PostgreSQL prompt immediately by typing:

psql

du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Currently, there is only one default role with many powerful privileges.

Removing Permissions in PostgreSQL

You can remove permissions by using the REVOKE command. The REVOKE command uses almost the same syntax as grant:

REVOKE permission_type ON table_name FROM user_name

You can use the same shorthand words, ALL and PUBLIC, in the command as well:

REVOKE INSERT ON demo FROM PUBLIC

The permissions you set before have now been revoked.

Step 6 — Creating and Deleting Tables

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

As you can see, these commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

CREATE TABLE playground 
    equip_id serial PRIMARY KEY,
     varchar  NOT NULL,
    color varchar  NOT NULL,
    location varchar check location  , , , , , , , ,
    install_date 

This command will create a table that inventories playground equipment. The first column in the table will hold equipment ID numbers of the serial type, which is an auto-incrementing integer. This column also has the constraint of PRIMARY KEY which means that the values within it must be unique and not null.

The next two lines create columns for the equipment type and color respectively, neither of which can be empty. The line after these creates a location column as well as a constraint that requires the value to be one of eight possible values. The last line creates a date column that records the date on which you installed the equipment.

For two of the columns (equip_id and install_date), the command doesn’t specify a field length. The reason for this is that some data types don’t require a set length because the length or format is implied.

You can see your new table by typing:

                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type which you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.

If you want to see just the table without the sequence, you can type:

dt

          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

With a table at the ready, let’s use it to practice managing data.

Indexes

An index helps retrieve rows from a table and its use is efficient only if the number of rows to be retrieved is relatively small. Adding an index to a column will allow you to query the data faster, but data inserts will be slower.

A partial index covers just a subset of a table’s data. It is an index with a WHERE clause. By reducing the index size (less storage, easier to maintain, faster to scan), its efficiency increases.

sequential scan: the database searches over all of the data before returning the results.

CREATE INDEX widgets_paid_index ON widgets(paid) WHERE paid IS TRUE;

Types

• B-Tree is the default index (compatible with all data types) and it creates a balanced tree i.e. amount of data on both sides of the tree is roughly the same. The number of levels that must be traversed to find rows is always similar. B-Tree indexes are well suited for equality and range queries.
• Hash indexes are only useful for equality comparisons and not transaction safe, they need to be manually rebuilt after crashes. There is no substantial advantage over using B-Tree
• Generalized Inverted (GIN) is useful when an index maps many values to one row. They are good for indexing array values and for implementing full-text search.
• Generalized Search Tree (GiST) allows to build general balanced tree structures. They can be used not only for equality & and range comparisons operations. They are used to index the geometric data types and full-text search.

Редактирование пользователя

1. Смена пароля

Рассмотрим несколько примеров смены пароля пользователя.

* в данном примере мы зададим пароль password для пользователя postgres.

С запросов ввода пароля:

=# \password postgres

* после ввода данной команды система потребует дважды ввести пароль для пользователя (в нашем примере, postgres).

Из командной строки Linux:

* по сути, мы выполняем также запрос в оболочке sql.

Defining Privileges Upon Role Creation

Now, you are ready to recreate the demo_role with altered permissions. You can do this by specifying the permissions you want after the main create clause like this:

CREATE ROLE role_name WITH assigned_permissions

To see the full list of the options, type:

h CREATE ROLE

Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

URL: https://www.postgresql.org/docs/14/sql-createrole.html

CREATE ROLE demo_role WITH LOGIN

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}

CREATE  role_name

The role is created with privilege automatically granted.

Step 9 — Updating Data in a Table

So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the “swing” record (this will match every swing in your table) and change its color to “red”. This could be useful if you gave the swing set a paint job:

UPDATE playground SET color   WHERE   

You can verify that the operation was successful by querying the data again:

SELECT * FROM playground

 equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

As this output indicates, your slide is now registered as being red.

Step 9 — Updating Data in a Table

So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the swing record (this will match every swing in your table) and change its color to red. This could be useful if you gave the swing set a paint job:

UPDATE playground SET color   WHERE   

You can verify that the operation was successful by querying the data again:

SELECT * FROM playground

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

As you can see, the slide is now registered as being red.

3 Проверьте место установки

[[email protected] software]
/lib64/security/pam_postgresok
/usr/share/doc/pam-/txts/README_postgresok
/usr/share/man/man8/pam_postgresok
/usr/pgsql-/share/postgresql--libs
/usr/share/selinux/targeted/postgresql2
/usr/share/doc/postgresql10-
/usr/share/doc/postgresql10-/COPYRIGHT
/usr/share/doc/postgresql10-/KNOWN_BUGS
/usr/share/doc/postgresql10-/MISSING_FEATURES
/usr/share/doc/postgresql10-/README-dist
/usr/share/doc/postgresql10-/bug
/usr/share/selinux/devel/include/services/postgresql
/etc/pam/postgresql
/etc/rc/init/postgresql-
/usr/pgsql-/bin/postgres
/usr/pgsql-/share/locale/de/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/es/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/fr/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/it/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/ko/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/pl/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/ru/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/sv/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/tr/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/locale/zh_CN/LC_MESSAGES/postgres-mo
/usr/pgsql-/share/man/man1/postgres
/usr/pgsql-/share/postgres
/usr/pgsql-/share/postgres
/usr/pgsql-/share/postgres
/usr/pgsql-/share/postgresql
/var/run/postgresql

Step 1 — Installing PostgreSQL

Ubuntu’s default repositories contain Postgres packages, so you can install these using the apt packaging system.

Since this is your first time using apt in this session, refresh your local package index. Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:

  update
   postgresql postgresql-contrib

Ensure that the server is running using the systemctl start command:

 systemctl start postgresql.service

Now that the software is installed and running, we can go over how it works and how it may be different from similar database management systems you may have used.

Проверьте, установлен ли PostgreSQL.

rpm -qa 
rpm -qal 

Если он был установлен, используйте команду rpm -e для удаления.

rpm -e postgresql94-contrib--PGDG686_64 postgresql94-server--PGDG686_64 Удалить
rpm -e postgresql94-libs--PGDG686_64 postgresql94--PGDG686_64 Удалить

Step 7 — Adding, Querying, and Deleting Data in a Table

Now that you have a table, you can insert some data into it.

As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column, like this:

INSERT INTO playground type, color, location, install_date VALUES , , , 
INSERT INTO playground type, color, location, install_date VALUES , , , 

You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.

Another thing to keep in mind is that you do not enter a value for the equip_id column. This is because this is automatically generated whenever a new row in the table is created.

Retrieve the information you’ve added by typing:

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

This output indicates that your equip_id has been filled in successfully and that all of your other data has been organized correctly.

If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:

DELETE FROM playground WHERE   

Query the table again:

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Notice that your slide is no longer a part of the table.

Creating Roles in PostgreSQL

There are a number of different ways to create roles for Postgres. It is possible to create roles from within Postgres, or from the command line.

Creating Roles From Within PostgreSQL

CREATE ROLE new_role_name

To demonstrate this, create a new role called demo_role:

CREATE ROLE demo_role

du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Creating Roles from the Command Line

First, exit out of the PostgreSQL command prompt for a moment by typing:

Then, log into the postgres account:

   postgres

createuser 

The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to your specifications:

Enter name of role to add: test_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Log back into your psql Postgres prompt:

psql

Then execute the du command to reveal the differences between the two new roles. This command starts with \ because it is a psql specific meta-command that is processed by psql itself and not by PostgreSQL:

du

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 demo_role | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test_user |                                                            | {}

Varia

PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. An application connects to pgbouncer as if it were a PostgreSQL server, pgbouncer creates a connection to the actual server or it reuses one of its existing connections. Its aim is to lower the performance impact of opening new connections to PostgreSQL server.

WAL

PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster’s data directory. The log records every change made to the database’s data files.

Step 7 — Adding, Querying, and Deleting Data in a Table

Now that you have a table, you can insert some data into it. As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column, like this:

INSERT INTO playground type, color, location, install_date VALUES , , , 
INSERT INTO playground type, color, location, install_date VALUES , , , 

You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.

Another thing to keep in mind is that you do not enter a value for the equip_id column. This is because this is automatically generated whenever you add a new row to the table.

Retrieve the information you’ve added by typing:

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Here, you can see that your equip_id has been filled in successfully and that all of your other data has been organized correctly.

If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:

DELETE FROM playground WHERE   

Query the table again:

SELECT * FROM playground

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Notice that the slide row is no longer a part of the table.

Выберите систему

Здесь выберите семейство Linux Red Hat (включая варианты CentOS / Fedora / Scientific / Oracle)

Step 3 — Creating a New Role

createuser 

If, instead, you prefer to use sudo for each command without switching from your normal account, type:

  postgres createuser 

Enter name of role to add: sammy

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

You can get more control by passing some additional flags. Check out the options by looking at the man page:

 createuser

Your installation of Postgres now has a new role, but you have not yet added any databases. The next section describes this process.

Prerequisites

With your environment prepared and Postgres running on your server, you can begin learning about how Postgres handles permissions.

Deleting Roles In PostgreSQL

DROP ROLE role_name

To demonstrate, delete the demo_role role by typing:

DROP ROLE demo_role

ERROR:  role "demo_role" does not exist

DROP ROLE IF EXISTS role_name

With this option specified, the command will complete successfully regardless of the validity of the role. Trying to remove the demo_role with the above commands will result in this:

DROP ROLE IF EXISTS demo_role

NOTICE:  role "demo_role" does not exist, skipping
DROP ROLE

The role is now deleted.

Настройка прав доступа к базе с помощью групп

Сначала создадим групповую роль:

* данной командой создана группа myRole с минимальными правами.

Теперь добавим ранее созданного пользователя dmosk в эту группу:

=# GRANT «myRole» TO dmosk;

Подключимся к базе данных, для которой хотим настроить права

=# \c database1

и предоставим все права для группы myRole всем таблицам базы database1

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP «myRole»;

Date and time

Dates

clock_timestamp() returns current value. now() always returns current value, unless in a transaction, in which case it returns the value from the beginning of the transaction.

Convert datetimes between timezones

SELECT now() AT TIME ZONE 'GMT';
SELECT now() AT TIME ZONE 'PST';

Get to know the day of the week for a given date

SELECT extract(DAY FROM now());

Specify time interval

• 3 days ago: SELECT now() - interval '3 days';
• 4 hours ago: SELECT now() - interval '4 hours';
• 2 days and 7 hours ago SELECT now() - interval ‘2 days 7 hours';

Step 2 — Using PostgreSQL Roles and Databases

There are a few ways to utilize this account to access Postgres.

Switching Over to the postgres Account

Switch over to the postgres account on your server by typing:

   postgres

You can now access the PostgreSQL prompt immediately by typing:

psql

From there you are free to interact with the database management system as necessary.

Exit out of the PostgreSQL prompt by typing:

This will bring you back to the postgres Linux command prompt.

Accessing a Postgres Prompt Without Switching Accounts

You can also run the command you’d like with the postgres account directly with sudo.

  postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

Again, you can exit the interactive Postgres session by typing:

Many use cases require more than one Postgres role. Read on to learn how to configure these.

Logging In as a Different User in PostgreSQL

password test_user

psql  user_name  database_name  .0.1 

Here’s a brief breakdown of each item in the command:

• The user_name should be replaced with the username you want to connect with.
• The database_name should be the name of an existing database that you have access to.
• The -h 127.0.0.1 section is the part that specifies that you will be connecting to the local machine, but through a network interface, which allows you to authenticate even though your system username does not match.
• The -W flag tells PostgreSQL that you will be entering a password.

 psql  test_user  postgres  .0.1 

You will need to enter a password after this command.

Exit the current session:

 u - postgres psql

Next you’ll be granting permissions.

Step 1 — Installing PostgreSQL

Ubuntu’s default repositories contain Postgres packages, so you can install these using the apt packaging system.

If you’ve not done so recently, refresh your server’s local package index:

  update

Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:

   postgresql postgresql-contrib

Ensure that the server is running using the systemctl start command:

 systemctl start postgresql.service

Now that the software is installed and running, we can go over how it works and how it may be different from other relational database management systems you may have used.

Создание нового пользователя

Для того, чтобы была возможность подключения к СУБД PostgreSQL от нового пользователя, необходимо создать данного пользователя, назначить ему права, выполнить настройку файла pg_hba.conf.

1. Создание пользователя

а) Добавление новой роли (пользователя) из оболочки SQL:

* в примере создана роль dmosk с паролем myPassword.

б) Добавление новой роли (пользователя) из командной строки Linux:

2. Назначение прав на использование базы данных

Даем права на базу командой:

=# GRANT ALL PRIVILEGES ON DATABASE «database1» to dmosk;

Теперь подключаемся к базе, к которой хотим дать доступ:

=# \c database1

* в примере подсоединимся к базе с названием database1.

а) Так мы добавим все права на использование всех таблиц в базе database1 учетной записи dmosk:

database1=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO «dmosk»;

* в большинстве случаев, используется схема по умолчанию public. Но администратор может создать новую схему. Это нужно учитывать при назначении прав.

Посмотреть список всех схем можно командой:

б) Также можно дать доступ к базе для определенных таблиц:

database1=# GRANT ALL PRIVILEGES ON TABLE table1 IN SCHEMA public TO «dmosk»;

* в данном примере мы даем права на таблицу table1.

3. Настройка файла pg_hba.conf

Для возможности подключиться к СУБД от созданного пользователя, необходимо проверить настройки прав в конфигурационном файле pg_hba.conf.

Для начала смотрим путь до него:

=# SELECT current_setting(‘hba_file’);

В ответ мы получим, что-то на подобие:

Выходим из оболочки sql:

Возвращается в командную строку суперпользователя:

Добавляем права на подключение нашему созданному пользователю:

* в данном примере мы разрешили подключаться пользователю dmosk ко всем базам на сервере (all) от узла 127.0.0.1 (localhost) с требованием пароля (md5).
* необходимо, чтобы данная строка была выше строки, которая прописана по умолчанию
host    all             all             127.0.0.1/32            ident.

В файле уже может быть строка на подобие:

host    all      all     127.0.0.1/32 md5

Это значит, что у нас будут права на подключение с локального хоста под всеми пользователями с использованием пароля.

После перезапускаем службу:

systemctl restart postgresql-9.6

* в данном примере установлен postgresql версии 9.6, для разных версий на разных операционных системах команды для перезапуска сервиса могут быть разные.

4. Проверка

Для теста пробуем подключиться к PostgreSQL с помощью созданного пользователя:

psql -U dmosk -d database1 -h 127.0.0.1

* в данном примере мы подключается к локальному серверу 127.0.0.1 под пользователем dmosk и базе database1, на которую мы дали ему права.

4 Просмотрите информацию PGDATA в файле /etc/rc. d/init. d/postgresql-10 и отобразите номер строки.

[[email protected] software]
:# Get rid of duplicate PGDATA assignment.
:PGDATA=/var/lib/pgsql//data
: PGDATA
:    # Check for the PGDATA structure
:     [   ] && [   ]
:            # Check version of existing PGDATA
:             [ x`cat ` != x ]
:            
:             " is missing. Use \"service  initdb\" to initialize the cluster first."
:      postgres -c 
:    pid=`head -n   >/dev/null`
:              postgres -c "/pg_ctl stop -D '' -s -m fast" > /dev/null >& < /dev/null
:             [   ]
:                     [ !    ! -h  ]
:                            mkdir -p  ||  
:                            chown postgres:postgres 
:                            chmod go-rwx 
:                    # Clean up SELinux tagging for PGDATA
:                    [ -x /sbin/restorecon ] && /sbin/restorecon 
:                    initdbcmd="/initdb --pgdata='' --auth='ident' "
:                    mkdir 
:                    chown postgres:postgres 
:                    chmod go-rwx 
:                    [   ] && _success
:                    [ !   ] && _failure
:OLDPGDATA=` sed -n  /etc/init.d/postgresql-`
:NEWPGDATA=` sed -n  /etc/init.d/postgresql-`
:su  postgres -c "/pg_upgrade -b /usr/pgsql-/bin/ -B / -d  -D  -p  -P  -c"
:              -D  \
:      postgres -c "/pg_ctl reload -D '' -s" > /dev/null >& < /dev/null
:      postgres -c "/pg_ctl promote -D '' -s" > /dev/null >& < /dev/null
[[email protected] software]

На приведенном выше рисунке показано, что место установки файла базы данных по умолчанию: / var / lib / pgsql / 10 / data.
Если вам нужно изменить место хранения файла базы данных, вы можете изменить его здесь. Далее будет показано, как изменить место хранения файла базы данных по умолчанию. Сначала проверьте, есть ли в этом каталоге файлы базы данных:

[root software]# ls -a /var/lib/pgsql/10/data
.  ..
[root software]

Обратите внимание, что после завершения установки postgresql каталог по умолчанию для хранения файлов базы данных пуст.

Conclusion

You are now set up with PostgreSQL on your Ubuntu 18.04 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres: