Создать сервер postgresql linux

Install and configure PostgreSQL

PostgreSQL (also known as Postgres) is an object-relational database system that has the features of traditional commercial database systems with enhancements to be found in next-generation database management systems (DBMS).

Installation

To install PostgreSQL, run the following command in the command prompt:

sudo apt install postgresql 

The database service is automatically configured with viable defaults, but can be customised based on your specific needs.

Configuration

PostgreSQL supports multiple client authentication methods. In Ubuntu, peer is the default authentication method used for local connections, while scram-sha-256 is the default for host connections (this used to be md5 until Ubuntu 21.10). Please refer to the PostgreSQL Administrator’s Guide if you would like to configure alternatives like Kerberos.

The following discussion assumes that you wish to enable TCP/IP connections and use the MD5 method for client authentication. PostgreSQL configuration files are stored in the /etc/postgresql//main directory. For example, if you install PostgreSQL 14, the configuration files are stored in the /etc/postgresql/14/main directory.

Tip:
To configure IDENT authentication, add entries to the /etc/postgresql/*/main/pg_ident.conf file. There are detailed comments in the file to guide you.

By default only connections from the local system are allowed, to enable all other computers to connect to your PostgreSQL server, edit the file /etc/postgresql/*/main/postgresql.conf . Locate the line: #listen_addresses = ‘localhost’ and change it to * :

Note:
‘*’ will allow all available IP interfaces (IPv4 and IPv6), to only listen for IPv4 set ‘0.0.0.0’ while ‘::’ allows listening for all IPv6 addresses.

For details on other parameters, refer to the configuration file or to the PostgreSQL documentation for information on how they can be edited.

Now that we can connect to our PostgreSQL server, the next step is to set a password for the postgres user. Run the following command at a terminal prompt to connect to the default PostgreSQL template database:

sudo -u postgres psql template1 

The above command connects to PostgreSQL database template1 as user postgres. Once you connect to the PostgreSQL server, you will be at an SQL prompt. You can run the following SQL command at the psql prompt to configure the password for the user postgres.

ALTER USER postgres with encrypted password 'your_password'; 

After configuring the password, edit the file /etc/postgresql/*/main/pg_hba.conf to use scram-sha-256 authentication with the postgres user, allowed for the template1 database, from any system in the local network (which in the example is 192.168.122.1/24) :

hostssl template1 postgres 192.168.122.1/24 scram-sha-256 

Note:
The config statement ‘hostssl’ used here will reject tcp connections that would not use ssl. Postgresql in Ubuntu has the ssl feature built in and configured by default, so it works right away. On your postgresql server this uses the certificate created by ‘ssl-cert’ package which is great, but for production use you should consider updating that with a proper certificate from a recognized CA.

Finally, you should restart the PostgreSQL service to initialise the new configuration. From a terminal prompt enter the following to restart PostgreSQL:

sudo systemctl restart postgresql.service 

Warning:
The above configuration is not complete by any means. Please refer to the PostgreSQL Administrator’s Guide to configure more parameters.

You can test server connections from other machines by using the PostgreSQL client as follows, replacing the domain name with your actual server domain name or IP address:

sudo apt install postgresql-client psql --host your-servers-dns-or-ip --username postgres --password --dbname template1 

Streaming replication

PostgreSQL has a nice feature called Streaming Replication which provides the capability to continuously ship and apply the Write-Ahead Log (WAL) XLOG records to some number of standby servers in order to keep them current. Here is presented a very basic and simple way to replicate a PostgreSQL server (main) to a standby server.

First, create a replication user in the main server to be used from the standby server:

sudo -u postgres createuser --replication -P -e replicator 

Let’s configure the main server to turn on the streaming replication. Open the file /etc/postgresql/*/main/postgresql.conf and make sure you have the following lines:

listen_addresses = '*' wal_level = replica 

Also edit the file /etc/postgresql/*/main/pg_hba.conf to add an extra line to allow the standby server connection for replication (that is a special keyword) using the replicator user:

host replication replicator scram-sha-256 

Restart the service to apply changes:

sudo systemctl restart postgresql 

Now, in the standby server, let’s stop the PostgreSQL service:

sudo systemctl stop postgresql 

Edit the /etc/postgresql/*/main/postgresql.conf to set up hot standby:

Back up the current state of the main server (those commands are still issued on the standby system):

sudo su - postgres # backup the current content of the standby server (update the version of your postgres accordingly) cp -R /var/lib/postgresql/14/main /var/lib/postgresql/14/main_bak # remove all the files in the data directory rm -rf /var/lib/postgresql/14/main/* pg_basebackup -h -D /var/lib/postgresql/14/main -U replicator -P -v -R 

After the above this will have done a full single pass copying the content of the main database onto the local system being the standby. In the pg_basebackup command the flags represent the following:

• -h : The hostname or IP address of the main server
• -D : The data directory
• -U : The user to be used in the operation
• -P : Turns on progress reporting
• -v : Enables verbose mode
• -R : Creates a standby.signal file and appends connection settings to postgresql.auto.conf

Finally, let’s start the PostgreSQL service on standby server:

sudo systemctl start postgresql 

To make sure it is working, go to the main server and run the following command:

sudo -u postgres psql -c "select * from pg_stat_replication;" 

As mentioned, this is a very simple introduction, there are way more great details in the upstream documentation about the configuration of replication as well as further High Availability, Load Balancing, and Replication.

To test the replication you can now create a test database in the main server and check if it is replicated in the standby server:

sudo -u postgres createdb test # on the main server sudo -u postgres psql -c "\l" # on the standby server 

You need to be able to see the test database, that was created on the main server, in the standby server.

Backups

PostgreSQL databases should be backed up regularly. Refer to the PostgreSQL Administrator’s Guide for different approaches.

Resources

• As mentioned above, the PostgreSQL Administrator’s Guide is an excellent resource. The guide is also available in the postgresql-doc package. Execute the following in a terminal to install the package:

sudo apt install postgresql-doc 

This package provides further man pages on postgresql ‘dblink’ and ‘server programming interface’ as well as the html guide that you’d find upstream. To view the guide enter xdg-open /usr/share/doc/postgresql-doc-*/html/index.html or point your browser at it.

• For general SQL information see the O’Reilly books Getting Started with SQL: A Hands-On Approach for Beginners by Thomas Nield as an entry point and SQL in a Nutshell as a quick reference.
• Also, see the PostgreSQL Ubuntu Wiki page for more information.

Источник

How To Install PostgreSQL on Ubuntu 22.04 [Quickstart]

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.

This guide demonstrates how to quickly get Postgres up and running on an Ubuntu 22.04 server, from installing PostgreSQL to setting up a new user and database. If you’d prefer a more in-depth tutorial on installing and managing a PostgreSQL database, see How To Install and Use PostgreSQL on Ubuntu 22.04.

Prerequisites

To follow along with this tutorial, you will need one Ubuntu 22.04 server that has been configured by following our Initial Server Setup for Ubuntu 22.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.

Step 1 — Installing PostgreSQL

To install PostgreSQL, first refresh your server’s local package index:

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

Press Y when prompted to confirm installation. If you are prompted to restart any services, press ENTER to accept the defaults and continue.

Step 2 — Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called “roles” to handle authentication and authorization. These are, in some ways, similar to regular Unix-style users and groups.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres. One way is to switch over to the postgres account on your server by running the following command:

Then you can access the Postgres prompt by running:

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

To exit out of the PostgreSQL prompt, run the following:

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo :

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

Again, you can exit the interactive Postgres session by running the following:

Step 3 — Creating a New Role

If you are logged in as the postgres account, you can create a new role by running the following command:

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

Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.

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

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.

This means that if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called “sammy” by default. You can create the appropriate database with the createdb command.

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

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

Step 5 — Opening a Postgres Prompt with the New Role

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

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

Or, you can do this inline:

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

If you want your user to connect to a different database, you can do so by specifying the database like the following:

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

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

Conclusion

You are now set up with PostgreSQL on your Ubuntu 22.04 server. If you’d like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Источник