OpenSUSE installs PostgreSQL

 

Environment: openSUSE Leap 42.2

Since Ubuntu abandoned Unity to Gnome, I have abandoned Ubuntu to open SUSE.
Leap’s software version is a bit old (for example, Vim still stays at 7.4), and the benefits are stability, elegance and ease of mind. The rolling release of Tumbleweed is still a bit short of breath. After trial, it feels unstable and unsuitable for daily use.
OpenSUSE’s KDE desktop is excellent. I use Windows for work, Mac for entertainment, Linux for code writing. I think openSUSE’s KDE is the best desktop.

Students who are still using Ubuntu can refer to it.Yan Feng FengWrite PostgreSQL beginner introductory.

install

Install client:

$ sudo zypper in postgresql

Install the server:

$ sudo zypper in postgresql-server

The interactive client program ispsqlThe server program ispostgres。

After installation, Linux has one more userpostgresBut the user did not set a password. Now set a password for it:

$ sudo passwd postgres
<prompt for a new password>

Next switch to the userpostgresThen create a database cluster.

(adam@linux) $ su postgres
<Enter the password you just set up>

(postgres@linux) $ initdb -D /var/lib/pgsql/data

The database cluster is the location where the database is placed./var/lib/pgsql/dataIt is one of the recommended locations.

Startup service

Must switch topostgresUser:

(adam@linux) $ su postgres

adoptpg_ctlTool Startup Service:

(postgres@linux) $ pg_ctl start -D /var/lib/pgsql/data
Starting server process
2017-06-25 10:11:14 CST Log: Log Output Redirected to Log Collection Process
2017-06-25 10:11:14 CST prompt: subsequent log output will appear in the directory "pg_log"

pg_ctlIs a tool for initializing, starting, stopping, or controlling PostgreSQL servers.

Look at the process state:

$ ps aux | grep postgres
postgres  5765  0.0  0.2 213160 20404 pts/0    S    10:11   0:00 /usr/lib/postgresql94/bin/postgres -D /var/lib/pgsql/data
postgres  5768  0.0  0.0  68308  3112 ?        Ss   10:11   0:00 postgres: logger process
postgres  5770  0.0  0.0 213160  3628 ?        Ss   10:11   0:00 postgres: checkpointer process
postgres  5771  0.0  0.0 213160  5684 ?        Ss   10:11   0:00 postgres: writer process
postgres  5772  0.0  0.0 213160  3628 ?        Ss   10:11   0:00 postgres: wal writer process
postgres  5773  0.0  0.0 213560  6800 ?        Ss   10:11   0:00 postgres: autovacuum launcher process
postgres  5774  0.0  0.0  68304  4216 ?        Ss   10:11   0:00 postgres: stats collector process

adoptpg_ctlView status:

(postgres@linux) $ pg_ctl status -D /var/lib/pgsql/data
Pg_ctl: Server process running (PID: 5765)
/usr/lib/postgresql94/bin/postgres "-D" "/var/lib/pgsql/data"

Attention notpostgresThere is still no permission to view the status under the user:

(adam@linux) $ pg_ctl status -D /var/lib/pgsql/data
Pg_ctl: unable to access directory "/ var/lib/pgsql/data": insufficient permissions

To avoid using it every time-DOption to specify the directory of the database cluster, now add environment variablesPGDATA。
open.bashrcAdd the following two lines:

# PostgreSQL database cluster directory.
export PGDATA=/var/lib/pgsql/data

Next timepg_ctlThere is no need to specify-DNow.

(postgres@linux) $ pg_ctl status
Pg_ctl: Server process running (PID: 5765)
/usr/lib/postgresql94/bin/postgres "-D" "/var/lib/pgsql/data"

Role

Back to ordinary Linux users (for exampleadamWith client programpsqlTrying to interact:

(adam@linux) $ psql template1
Psql: fatal error: role "adam" does not exist

The prompt says that the role does not exist.
Role is a concept for PostgreSQL to manage database access rights.

Cut backpostgresUser, list all roles.

(postgres@linux) $ psql template1
psql (9.4.9)
Enter "help" to get help information.

template1=# SELECT rolname FROM pg_roles;
rolname
----------
postgres
(1 line)

There is currently only one role, postgres.
Create a new roleadam：

template1=# CREATE ROLE adam;
CREATE ROLE
template1=# SELECT  rolname FROM pg_roles;
rolname
----------
postgres
adam
(2 rows)

Try again with the useradamConnect:

(adam@linux) $ psql template1

Psql: fatal error: role "adam" is not allowed to log in

Looks like the role just createdadamNo,LOGINAccess to the database.
Re-create roles:

template1=# DROP ROLE adam;
DROP ROLE
template1=# CREATE ROLE adam WITH LOGIN CREATEDB PASSWORD '654321';

Subsequently, roles are availableadamLog in.

(adam@linux) $ psql -l
                                    List of databases
  Name | Owner | Character Coding | Proofreading Rule | Ctype | Access Rights
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
          |          |          |             |             | postgres=CTc/postgres
(3 rows)

psqladopt-UThe default role is the user name of Linux, so you can actually specify roles:

(adam@linux) $ psql -l -Uadam

Note that the database role name does not have to be the same as the Linux user name.

adopt-UOption to log in to the database in any role:

(adam@linux) $ psql -l -Upostgres

Create a database

(adam@linux) $ createdb mydb

(adam@linux) $ psql -l

  Name | Owner | Character Coding | Proofreading Rule | Ctype | Access Rights
-----------+----------+----------+-------------+-------------+-----------------------
mydb      | adam     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
...

Delete the database:

(adam@linux) $ dropdb mydb

Use the database:

(adam@linux) $ psql mydb

Of course,psqlIt’s just one way to use data.

Tags: data base, db, linux, pgsql, postgresql

 

https://developpaper.com/opensuse-installs-postgresql/