4,348
edits
m
no edit summary
mNo edit summary |
|||
| (10 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
= Initial set-up = | = Initial set-up = | ||
Install | Install (the default is v10.23 as of 2023-10-13) | ||
{{testedon|RHEL 8.8 (Ootpa)|2023-10-13}} | {{testedon|RHEL 8.8 (Ootpa)|2023-10-13}} | ||
| Line 8: | Line 8: | ||
$ sudo yum module list | grep postgresql | $ sudo yum module list | grep postgresql | ||
$ sudo yum install @postgresql | $ sudo yum install @postgresql | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 16: | Line 15: | ||
$ sudo yum install @postgresql:9.6 | $ sudo yum install @postgresql:9.6 | ||
</syntaxhighlight> | </syntaxhighlight> | ||
for the initial set-up | |||
<syntaxhighlight lang="bash"> | |||
$ sudo postgresql-setup --initdb | |||
* Initializing database in '/var/lib/pgsql/data' | |||
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log | |||
</syntaxhighlight> | |||
for setting up a password for the postgres account | |||
<syntaxhighlight lang="bash"> | |||
$ sudo passwd postgres | |||
Changing password for user postgres. | |||
New password: | |||
Retype new password: | |||
passwd: all authentication tokens updated successfully. | |||
</syntaxhighlight> | |||
enable PostgreSQL server at the boot time | |||
<syntaxhighlight lang="bash"> | |||
$ sudo systemctl enable postgresql | |||
</syntaxhighlight> | |||
= Prerequisite for psql console = | |||
change to postgres user account | |||
<syntaxhighlight lang="bash"> | |||
$ sudo -iu postgres | |||
</syntaxhighlight> | |||
log into postgres console | |||
<syntaxhighlight lang="bash"> | |||
$ psql | |||
psql (10.23) | |||
Type "help" for help. | |||
postgres=# | |||
</syntaxhighlight> | |||
= Configuration = | |||
In order to force password when logging into postgresql console change <code>ident</code> to <code>scram-sha-256</code> in <code>/var/lib/pgsql/data/pg_hba.conf</code> for IP local connections entries. This is the HBA (Host-based Authentication) configuration file for PostgreSQL. Restart afterwards. | |||
== Create a new PostgreSQL user account and a new DB == | |||
Create a Linux user account | |||
<syntaxhighlight lang="bash"> | |||
$ sudo useradd puser | |||
$ sudo passwd puser | |||
</syntaxhighlight> | |||
log in as the admin PostgreSQL user | |||
<syntaxhighlight lang="bash"> | |||
$ sudo -iu postgres | |||
</syntaxhighlight> | |||
create a new PostgreSQL role for puser Linux user | |||
<syntaxhighlight lang="bash"> | |||
$ createuser --interactive --pwprompt | |||
</syntaxhighlight> | |||
while still logged in as postgres user (admin PostgreSQL user) create a new database named appdb for puser | |||
<syntaxhighlight lang="bash"> | |||
$ createdb -O puser appdb | |||
</syntaxhighlight> | |||
= Usage = | |||
Log into PostgreSQL console using a specific user and DB | |||
<syntaxhighlight lang="bash"> | |||
$ sudo -iu puser | |||
$ psql -d appdata -U puser | |||
</syntaxhighlight> | |||
Create a new table | |||
<syntaxhighlight lang="psql"> | |||
puser=> CREATE TABLE demo( | |||
id serial PRIMARY KEY, | |||
email VARCHAR (100) UNIQUE NOT NULL, | |||
name VARCHAR (50) UNIQUE NOT NULL | |||
); | |||
</syntaxhighlight> | |||
{| class="wikitable sortable" | |||
|+ PostgreSQL console commands | |||
|- | |||
! Command !! Action !! Description or Notes | |||
|- | |||
| class="cli" | \q || Quit || | |||
|- | |||
| class="cli" | \conninfo || Connection information || Get current connection information | |||
|- | |||
| class="cli" | \d || List || List tables, views, and sequences | |||
|- | |||
| class="cli" | \dt || List tables || | |||
|} | |||
<syntaxhighlight lang="bash"> | |||
</syntaxhighlight> | |||
= Resources = | = Resources = | ||
[[Introduction to PostgreSQL for MySQL Users]] | [[Introduction to PostgreSQL for MySQL Users]] | ||