List out the available streams for the postgresql module using the dnf command:
[root@MaxwellDBA home]# dnf module list postgresql
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:37:44 ago on Tue 30 Aug 2022 12:23:12 PM CST.
CentOS-8.5.2111 - AppStream - mirrors.aliyun.com
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
postgresql 13 client, server [d] PostgreSQL server and client module
CentOS Linux 8 - AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
postgresql 13 client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalledTo enable the module stream for Postgres version 12, run the following command:
[root@MaxwellDBA home]# sudo dnf module enable postgresql:12
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:41:11 ago on Tue 30 Aug 2022 12:23:12 PM CST.
Dependencies resolved.
==========================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================
Enabling module streams:
postgresql 12
Transaction Summary
==========================================================================================================================================================================
Is this ok [y/N]: y
Complete!
[root@MaxwellDBA home]#After enabling the version 12 module stream, you can install the postgresql-server package to install PostgreSQL 12 and all of its dependencies:
[root@MaxwellDBA home]# sudo dnf install postgresql-server
Repository extras is listed more than once in the configuration
Invalid configuration value: failovermethod=priority in /etc/yum.repos.d/CentOS-epel.repo; Configuration: OptionBinding with id "failovermethod" does not exist
Last metadata expiration check: 0:43:08 ago on Tue 30 Aug 2022 12:23:12 PM CST.
Dependencies resolved.
==========================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================
Installing:
postgresql-server x86_64 12.9-1.module_el8.5.0+1061+5aa9de58 AppStream 5.6 M
Installing dependencies:
libpq x86_64 13.3-1.el8_4 AppStream 197 k
postgresql x86_64 12.9-1.module_el8.5.0+1061+5aa9de58 AppStream 1.5 M
Transaction Summary
==========================================================================================================================================================================
Install 3 Packages
Total download size: 7.3 M
Installed size: 28 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): libpq-13.3-1.el8_4.x86_64.rpm 1.1 MB/s | 197 kB 00:00
(2/3): postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64.rpm 4.3 MB/s | 1.5 MB 00:00
(3/3): postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64.rpm 6.9 MB/s | 5.6 MB 00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 9.0 MB/s | 7.3 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libpq-13.3-1.el8_4.x86_64 1/3
Installing : postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 2/3
Running scriptlet: postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 3/3
Installing : postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 3/3
Running scriptlet: postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 3/3
[/usr/lib/tmpfiles.d/postgresql.conf:1] Line references path below legacy directory /var/run/, updating /var/run/postgresql → /run/postgresql; please update the tmpfiles.d/ drop-in file accordingly.
Verifying : libpq-13.3-1.el8_4.x86_64 1/3
Verifying : postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 2/3
Verifying : postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 3/3
Installed:
libpq-13.3-1.el8_4.x86_64 postgresql-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64 postgresql-server-12.9-1.module_el8.5.0+1061+5aa9de58.x86_64
Complete!
[root@MaxwellDBA home]# Step 2 — Creating a New PostgreSQL Database Cluster
You have to create a new PostgreSQL database cluster before you can start creating tables and loading them with data. A database cluster is a collection of databases that are managed by a single server instance. Creating a database cluster consists of creating the directories in which the database data will be placed, generating the shared catalog tables, and creating the template1 and postgres databases.
The template1 database is a template of sorts used to create new databases; everything that is stored in template1, even objects you add yourself, will be placed in new databases when they’re created. The postgres database is a default database designed for use by users, utilities, and third-party applications.
The Postgres package we installed in the previous step comes with a handy script called postgresql-setup which helps with low-level database cluster administration. To create a database cluster, run the script using sudo and with the --initdb option:
[root@MaxwellDBA home]# sudo postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@MaxwellDBA home]# Now start the PostgreSQL service using systemctl:
[root@MaxwellDBA home]# sudo systemctl start postgresql
[root@MaxwellDBA home]# Then, use systemctl once more to enable the service to start up whenever the server boots:
[root@MaxwellDBA home]# sudo systemctl enable postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@MaxwellDBA home]# Now that PostgreSQL is up and running, we will go over using roles to learn how Postgres works and how it is different from similar database management systems you may have used in the past.
Step 3 — Using PostgreSQL Roles and Databases
PostgreSQL uses a concept called roles to handle client authentication and authorization. These are in some ways similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term role.
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. In order to use PostgreSQL, you can log in to that account.
There are a few ways to use this account to access the PostgreSQL prompt.
Switching Over to the postgres Account
Switch over to the postgres account on your server by typing:
[root@MaxwellDBA home]# sudo -i -u postgresYou can now access a Postgres prompt immediately by typing:
[postgres@MaxwellDBA ~]$ psql
psql (12.9)
Type "help" for help.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:
postgres=# \qThis will bring you back to the postgres account’s Linux command prompt. Now return to your original account with the following:
[postgres@MaxwellDBA ~]$ exit
logout
[root@MaxwellDBA home]#Accessing a Postgres Prompt Without Switching Accounts
You can also run commands with the postgres account directly using sudo.
For instance, in the previous example, you were instructed to access the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. As an alternative, you could do this in one step by running the single command psql as the postgres user with sudo, like this:
[root@MaxwellDBA home]# sudo -u postgres psql
psql (12.9)
Type "help" for help.
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 12.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)
postgres=# \qIn this step, you used the postgres account to reach the psql prompt. But many use cases require more than one Postgres role. Read on to learn how to configure new roles.
axwellDBA home]# sudo -i -u postgres
[postgres@MaxwellDBA ~]$ createuser --interactive
Enter name of role to add: maxwell
Shall the new role be a superuser? (y/n) y
[postgres@MaxwellDBA ~]$ If, instead, you prefer to use sudo for each command without switching from your normal account, type:
sudo -u postgres createuser --interactiveYou can get more control by passing some additional flags. Check out the options by looking at the man page for createuser:
man createuserStep 5 — 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 maxwell, that role will attempt to connect to a database which is also called maxwellby default. You can create such a database with the createdb command.
If you are logged in as the postgres account, you would type something like:
[postgres@MaxwellDBA ~]$ createdb maxwelldb
[postgres@MaxwellDBA ~]$ If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:
sudo -u postgres createdb maxwelldbThis flexibility provides multiple paths for creating databases as needed.
Now that you’ve created a new database, you will log in to it with your new role.
[root@MaxwellDBA home]# sudo -i -u postgres
[postgres@MaxwellDBA ~]$ createdb maxwell
[postgres@MaxwellDBA ~]$ exit
logout
[root@MaxwellDBA home]# sudo -i -u maxwell
[maxwell@MaxwellDBA ~]$ psql
psql (12.9)
Type "help" for help.
maxwell=# Once this new account is available, you can either switch over and then connect to the database by first typing:
[root@MaxwellDBA home]# sudo -i -u maxwell
[maxwell@MaxwellDBA ~]$ psqlIf you want your user to connect to a different database, you can do so by including the -d flag and specifying the database, like this:
[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.
maxwelldb=# Once logged in, you can check your current connection information by typing:
[maxwell@MaxwellDBA ~]$ psql -d maxwelldb
psql (12.9)
Type "help" for help.
maxwelldb=# \conninfo
You are connected to database "maxwelldb" as user "maxwell" via socket in "/var/run/postgresql" at port "5432".
maxwelldb=# This is useful if you are connecting to non-default databases or with non-default users.
Having connected to your database, you can now try out creating and deleting tables.
Step 7 — 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, you will make a table that describes some playground equipment.
The basic syntax for this command is as follows:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);maxwelldb=# CREATE TABLE playground (
maxwelldb(# equip_id serial PRIMARY KEY,
maxwelldb(# type varchar (50) NOT NULL,
maxwelldb(# color varchar (25) NOT NULL,
maxwelldb(# location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
maxwelldb(# install_date date
maxwelldb(# );
CREATE TABLE
maxwelldb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+---------
public | playground | table | maxwell
public | playground_equip_id_seq | sequence | maxwell
(2 rows)
maxwelldb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+---------
public | playground | table | maxwell
(1 row)
maxwelldb=# Step 8 — Adding, Querying, and Deleting Data in a Table
Step 9 — Adding and Deleting Columns from a Table
Step 10 — Updating Data in a Table
maxwelldb=# INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT 0 1
maxwelldb=# INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
INSERT 0 1
maxwelldb=# 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)
maxwelldb=# DELETE FROM playground WHERE type = 'slide';
DELETE 1
maxwelldb=# select * from playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
maxwelldb=# select * from playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
maxwelldb=# ALTER TABLE playground ADD last_maint date;
ALTER TABLE
maxwelldb=# select * from playground;
equip_id | type | color | location | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
2 | swing | yellow | northwest | 2018-08-16 |
(1 row)
maxwelldb=# ALTER TABLE playground DROP last_maint;
ALTER TABLE
^
maxwelldb=# select * from playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
maxwelldb=# UPDATE playground SET color = 'red' WHERE type = 'swing';
UPDATE 1
maxwelldb=# select * from playground;
equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2018-08-16
(1 row)
maxwelldb=#
1861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
