Some problems in Ubuntu 18.04 PostgreSQL 11 apt default installation

First, install the software by default (this article takes PostgreSQL 11.1 as an example, and other versions are similar).

sudo apt install postgresql-11

Wait for the software to install automatically and complete the configuration to start the service.

The service status is as follows:

vmware@vmware-virtual-machine:~$ service postgresql status
‚óŹ postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2018-12-13 17:16:01 CST; 24min ago
 Main PID: 19530 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 2289)
   CGroup: /system.slice/postgresql.service

12 Month 1317:16:01 vmware-virtual-machine systemd[1]: Starting PostgreSQL RDBMS...
12 Month 1317:16:01 vmware-virtual-machine systemd[1]: Started PostgreSQL RDBMS.
vmware@vmware-virtual-machine:~$ 

After installation, by default:

(1) create "postgres" Linux user

(2) create "postgres" unknown password default database administrator account

(3) create "postgres" Database

Some default configuration folders are as follows

#configuration file
vmware@vmware-virtual-machine:~$ ls /etc/postgresql/11/main
conf.d  environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf
#data file
vmware@vmware-virtual-machine:~$ sudo ls /var/lib/postgresql/11/main/
base	pg_commit_ts  pg_logical    pg_notify	 pg_serial     pg_stat	    pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.pid
global	pg_dynshmem   pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc	 PG_VERSION   pg_xact  postmaster.opts
vmware@vmware-virtual-machine:~$

Log in to the PostgreSQL database (two methods).

#Method 1
vmware@vmware-virtual-machine:~$ sudo -u postgres psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
//Enter help for help information

postgres=# \l
                                     //Database list
   //Name | owner | character code | proofing rule | Ctype | access permission        
-----------+----------+----------+-------------+-------------+-----------------------
 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 Row record)

postgres=# \q
#Method 2
vmware@vmware-virtual-machine:~$ sudo -i
root@vmware-virtual-machine:~# su - postgres 
postgres@vmware-virtual-machine:~$ psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
//Type help for help information

postgres=# 

By default, the database can be accessed locally, and the local port is monitored by default. Now modify the database intranet access restrictions. (192.168.0.1/16 network segment is added in this paper; 0.0.0.0 represents all hosts, not recommended)

#Turn on monitoring
 53 #------------------------------------------------------------------------------
 54 # CONNECTIONS AND AUTHENTICATION
 55 #------------------------------------------------------------------------------
 56 
 57 # - Connection Settings -
 58 listen_addresses='*'
#Host connection information
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.1/16          md5

Remember to reload the configuration file after modifying it.

root@vmware-virtual-machine:~# su - postgres 
postgres@vmware-virtual-machine:~$ psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
//Enter help for help information

postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 Row record)

postgres=# 

It should be noted that the password of the automatic installation database administrator is unknown and needs to be modified manually. Change the database password to 123456.

root@vmware-virtual-machine:~# su - postgres 
postgres@vmware-virtual-machine:~$ psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
//Enter help for help information

postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE
postgres=# 

Test database connection succeeded. (this article takes Microsoft Visual Studio as an example.)

Tags: Database Vmware PostgreSQL sudo

Posted on Mon, 02 Dec 2019 09:33:10 -0500 by craka