PostgreSQL Kindergarten

/ Editor Leon | 六小編

Original link: https://editor.leonh.space/2023/postgresql/

For the database, apart from SQLite, which is exquisitely designed and built in various languages ​​and platforms, I have always used MariaDB/MySQL for the simple reason that most people use it and follow the crowd, but recently I noticed some database cloud services Manufacturers are all based on PostgreSQL, and all bigwigs have unanimously praised PostgreSQL, and considering that the PostgreSQL ecosystem is large enough, and there is a serious new project at hand, they resolutely switched to PostgreSQL (relatively speaking, I don’t dare to switch to Firebird), here is a small note for a user with MariaDB / MySQL experience transferring to a PostgreSQL kindergarten class.

Install

In the PostgreSQL ecosystem, there are many third-party organizations that make installation packages, usually giving some additional tools, suites, and features, some of which are charged, and some are based on PostgreSQL and build their own database products on it. For example, EDB on the official website, others include bit.io, Timescale, Neon, Supabase, Citus, Greenplum, etc., see < PostgreSQL derived databases >.

PostgreSQL has experienced many years of development, and many versions are still in circulation. Which version to use first depends on which versions are supported , that is, there will be updates. Take PostgreSQL 11 as an example, it will end support in November 2023, and PostgreSQL 15 will not end support until 2027.

You can also refer to the function matrix to determine the appropriate version.

Install on Windows, there is no suspense, just go to the official website to download the installation package packaged by EDB, and the next step will be fine.

If it is Linux, the version in the Linux pre-installed package library may not be the latest version. Taking Ubuntu 22.04 as an example, the PostgreSQL version in the pre-installed package library is 14. If you want to install PostgreSQL 15, you must add the APT library maintained by PostgreSQL. For PostgreSQL Xiaobai, it is a matter of course to choose the latest version.

The installation steps in one go are as follows:

 # Create the file repository configuration: $ sudo sh -c ' echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list '  
# Import the repository signing key: $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -  
# Update the package lists: $ sudo apt-get update  
# Install the latest version of PostgreSQL. # If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': $ sudo apt-get install postgresql-15

in:

  • In the second step of adding the key, the system will report “Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).” It is just a warning and can be ignored. For more information, See Handling “apt-key is deprecated. Manage keyring files in trusted.gpg.d instead” in Ubuntu Linux .
  • In the third step, the system will report “W: https://ift.tt/Xt8H6GK Key is stored in legacy trusted.gpg keyring (/etc/apt/trusted.gpg), see the DEPRECATION section in apt-key(8) for details.”, the reason is the same as above, you can ignore it.
  • If you want to be fixed on PostgreSQL 15.X and do not want to bear the risk of skipping the main version update, please install postgresql-15 package. If you want to update with the main board, please install postgresql package. Here I choose postgresql-15 .

After loading:

  • There is an additional system account postgres, which is also the default management account of PostgreSQL.
  • There are two more configuration folders /etc/postgresql/ and /etc/postgresql-common.
  • There is an additional system service postgresql.service in /etc/systemd/system/multi-user.target.wants/postgresql.service, which will listen to port 5432.
  • PostgreSQL defaults locale as en_US.UTF-8 , encoding as UTF8 , and text search as english .
  • The real body of the database is in /var/lib/postgresql/15/, so if you know it, don’t mess around.

configuration

The main configuration file is in /etc/postgresql/15/main/postgresql.conf. There are many parameters in it. You can refer to ” Service Configuration Settings ” in the PostgreSQL manual or refer to the website POSTGRESQLCO.NF . Most of them can be left as they are. , the following is my personal change project.

connection and authentication (connectoins and authentication)

  • listen_addresses changed from 'localhost' to the machine’s own IP or '*' , so that PostgreSQL accepts external connections. Note that this is the IP address that PostgreSQL will listen on, not the whitelist address of the client.
  • unix_socket_directories = '/var/run/postgresql' , this item does not change, just let me know that PostgreSQL also accepts Unix socket connections.

Reporting and logging

  • log_timezone = 'Asia/Taipei' , this item does not change, just know it.

Client connection defaults

  • datestyle = 'iso, mdy' , this item does not change, just know it.
  • timezone = 'Asia/Taipei' , this item does not change, just know it.

Restart the service after changing:

 $ sudo systemctl restart postgresql

Enter PostgreSQL

Assuming that nothing has been configured just after pouring, you can only use the postgres account to enter:

 $ sudo -u postgres psql

Go in and you will see the text interactive interface of PostgreSQL.

Accounts and roles

PostgreSQL’s identity authentication configuration file is in /etc/postgres/15/main/pg_hba.conf, its configuration is similar to a table, the most basic is like this:

 # type database user auth-method [auth-options] local all postgres peer

Item-by-item description:

  • If the Type is local , it means to use the local Unix socket connection.
  • Database is all , which means that all databases are allowed to be read.
  • User is postgres , indicating the PostgreSQL account.
  • Auth-method is peer , which means to use the account authentication mechanism of the OS.

Taken together, the above means that “the PostgreSQL account postgres has the right to connect to PostgresSQL through the Unix socket, and is authenticated by the Linux account postgres, and can operate any database.”

Let’s look at the second example:

 # type database user auth-method [auth-options] local all all peer

This means “All PostgreSQL accounts can connect to PostgreSQL through the Unix socket, and authenticate with the currently logged in Linux account, and can operate any database.”

The so-called “all PostgreSQL accounts” here can be obtained through SQL query:

 SELECT rolname FROM pg_roles;

The result is as follows:

 rolname --------------------------- postgres pg_database_owner pg_read_all_data pg_write_all_data pg_monitor pg_read_all_settings pg_read_all_stats pg_stat_scan_tables pg_read_server_files pg_write_server_files pg_execute_server_program pg_signal_backend pg_checkpoint (13 rows)

It can be seen that except for the postgres added during installation, the remaining pg_xxx are pre-built roles in the system and are not used as login accounts.

Back to the previous question “What are all PostgreSQL accounts”? At present, only postgres is the only one, so I can’t get into PostgreSQL with the Linux account leon today, even if all is set on the authentication side.

It seems that the practical principle recommended by PostgreSQL is to only use the postgres account as the DBA, so on the local side, it is better to use sudo -u postgres psql to enter PostgreSQL obediently, and other applications should open separate accounts with less privileges for the application to use.

In terms of remote connection, see the following example, because TCP/IP is used, there are more fields related to IP:

 # type database user address auth-method [auth-options] host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256

These two addresses both mean localhost , that is, the local machine. The first line is IPv4, and the second line is IPv6. /IP to connect to PostgreSQL, and authenticate with SHA-256 encrypted password, and can operate any database.”

As mentioned earlier, switch to the postgres account at the Linux shell layer to enter PostgreSQL:

 $ sudo -u postgres psql

However, it is more convenient for some local database applications to use password authentication. If you want to add a password for the postgres account of PostgreSQL, you can do this:

 ALTER USER postgres PASSWORD ' myPassword ';

After setting the password, also change pg_hba.conf to let it accept Unix socket + password authentication, and add the settings in pg_hba.conf:

 # type database user auth-method [auth-options] local all all scram-sha-256 local all postgres peer local all all peer  
# type database user address auth-method [auth-options] host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256

The second line above is the setting that makes PostgreSQL accept Unix socket and authenticate with password.

Remember to restart the PostgreSQL service after changing.

 $ sudo systemctl restart postgresql

According to the above settings, you can use the TCP connection on this machine to authenticate with the account password. The connection string will look like this:

 postgresql://postgres:myPassword@localhost:5432/mydb

You can also use the Unix socket connection to authenticate with account secrets. The connection string looks like this:

 postgresql://postgres:myPassword@localhost/mydb?host=/var/run/postgresql/

But it should be noted that the connection string here may not be applicable to all, and the configuration format of each application may be different.

additional materials

– < localized collation in PG >.

This article is transferred from: https://editor.leonh.space/2023/postgresql/
This site is only for collection, and the copyright belongs to the original author.