ubuntu下安装PostgreSQL详细教程及简单操作

技术2023-05-16 104

ubuntu下安装PostgreSQL详细教程及简单操作

1.创建源2.更新源3.安装4.查看5.安装pgadmin36.查看所有用户名7.创建用户8.删除用户9.创建或删除数据库10.退出psql11.创建普通用户与数据库同名12.以用户名的身份连接数据库

1.创建源

# 1.创建源 #deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main zhangyanfeng@ubuntu:/etc/apt$ cd sources.list.d/ zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ ls sogoupinyin.list zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ sudo touch pgdg.list zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ ls pgdg.list sogoupinyin.list zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ vim pgdg.list zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ cat pgdg.list deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

2.更新源

#2.更新源 #wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - #sudo apt-get update zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ cat pgdg.list deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - OK zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ sudo apt-get update Hit:1 http://us.archive.ubuntu.com/ubuntu bionic InRelease Hit:2 http://us.archive.ubuntu.com/ubuntu bionic-updates InRelease Hit:3 http://us.archive.ubuntu.com/ubuntu bionic-backports InRelease Get:4 http://apt.postgresql.org/pub/repos/apt bionic-pgdg InRelease [84.6 kB] Hit:5 http://security.ubuntu.com/ubuntu bionic-security InRelease Get:6 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main i386 Packages [197 kB] Get:7 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 Packages [198 kB] Fetched 480 kB in 37s (12.9 kB/s) Reading package lists... Done zhangyanfeng@ubuntu:/etc/apt/sources.list.d$

3.安装

#3.安装 #sudo apt-get install postgresql zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ sudo apt-get install postgresql Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-4.15.0-101 linux-headers-4.15.0-101-generic linux-image-4.15.0-101-generic linux-modules-4.15.0-101-generic linux-modules-extra-4.15.0-101-generic Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: libpq-dev libpq5 pgdg-keyring postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat Suggested packages: postgresql-doc-12 postgresql-doc libjson-perl isag The following NEW packages will be installed: pgdg-keyring postgresql postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat The following packages will be upgraded: libpq-dev libpq5 2 upgraded, 7 newly installed, 0 to remove and 389 not upgraded. Need to get 17.0 MB of archives. After this operation, 54.5 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 libpq-dev amd64 12.3-1.pgdg18.04+1 [138 kB] Get:2 http://us.archive.ubuntu.com/ubuntu bionic-updates/main amd64 sysstat amd64 11.6.1-1ubuntu0.1 [295 kB] Get:3 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 libpq5 amd64 12.3-1.pgdg18.04+1 [173 kB] Get:4 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB] Get:5 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-common all 215.pgdg18.04+1 [87.4 kB] Get:6 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-12 amd64 12.3-1.pgdg18.04+1 [1,413 kB] Get:7 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-common all 215.pgdg18.04+1 [237 kB] Get:8 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-12 amd64 12.3-1.pgdg18.04+1 [14.6 MB] Get:8 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-12 amd64 12.3-1.pgdg18.04+1 [14.6 MB] Get:9 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql all 12+215.pgdg18.04+1 [63.1 kB] Fetched 10.2 MB in 44min 17s (3,825 B/s) Preconfiguring packages ... (Reading database ... 252697 files and directories currently installed.) Preparing to unpack .../0-libpq-dev_12.3-1.pgdg18.04+1_amd64.deb ... Unpacking libpq-dev (12.3-1.pgdg18.04+1) over (10.12-0ubuntu0.18.04.1) ... Preparing to unpack .../1-libpq5_12.3-1.pgdg18.04+1_amd64.deb ... Unpacking libpq5:amd64 (12.3-1.pgdg18.04+1) over (10.12-0ubuntu0.18.04.1) ... Selecting previously unselected package pgdg-keyring. Preparing to unpack .../2-pgdg-keyring_2018.2_all.deb ... Unpacking pgdg-keyring (2018.2) ... Selecting previously unselected package postgresql-client-common. Preparing to unpack .../3-postgresql-client-common_215.pgdg18.04+1_all.deb ... Unpacking postgresql-client-common (215.pgdg18.04+1) ... Selecting previously unselected package postgresql-client-12. Preparing to unpack .../4-postgresql-client-12_12.3-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-client-12 (12.3-1.pgdg18.04+1) ... Selecting previously unselected package postgresql-common. Preparing to unpack .../5-postgresql-common_215.pgdg18.04+1_all.deb ... Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' Unpacking postgresql-common (215.pgdg18.04+1) ... Selecting previously unselected package postgresql-12. Preparing to unpack .../6-postgresql-12_12.3-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-12 (12.3-1.pgdg18.04+1) ... Selecting previously unselected package postgresql. Preparing to unpack .../7-postgresql_12+215.pgdg18.04+1_all.deb ... Unpacking postgresql (12+215.pgdg18.04+1) ... Selecting previously unselected package sysstat. Preparing to unpack .../8-sysstat_11.6.1-1ubuntu0.1_amd64.deb ... Unpacking sysstat (11.6.1-1ubuntu0.1) ... Setting up sysstat (11.6.1-1ubuntu0.1) ... Creating config file /etc/default/sysstat with new version update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service. Processing triggers for ureadahead (0.100.0-20) ... Setting up libpq5:amd64 (12.3-1.pgdg18.04+1) ... Processing triggers for libc-bin (2.27-3ubuntu1) ... Processing triggers for systemd (237-3ubuntu10.6) ... Processing triggers for man-db (2.8.3-2) ... Setting up pgdg-keyring (2018.2) ... Removing apt.postgresql.org key from trusted.gpg: OK Setting up libpq-dev (12.3-1.pgdg18.04+1) ... Setting up postgresql-client-common (215.pgdg18.04+1) ... Setting up postgresql-common (215.pgdg18.04+1) ... Adding user postgres to group ssl-cert Creating config file /etc/postgresql-common/createcluster.conf with new version Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_au en_ca en_gb en_us en_za Removing obsolete dictionary files: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service. Setting up postgresql-client-12 (12.3-1.pgdg18.04+1) ... update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-12 (12.3-1.pgdg18.04+1) ... Creating new PostgreSQL cluster 12/main ... /usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locales COLLATE: en_US.UTF-8 CTYPE: en_US.UTF-8 MESSAGES: en_US.UTF-8 MONETARY: zh_CN.UTF-8 NUMERIC: zh_CN.UTF-8 TIME: zh_CN.UTF-8 The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/12/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Macau creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctlcluster 12 main start Ver Cluster Port Status Owner Data directory Log file 12 main 5432 down postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Setting up postgresql (12+215.pgdg18.04+1) ... Processing triggers for systemd (237-3ubuntu10.6) ... Processing triggers for ureadahead (0.100.0-20) ... zhangyanfeng@ubuntu:/etc/apt/sources.list.d$

4.查看

#psql --version #查看postgres安装情况（dpkg --list | grep postgresql） zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ dpkg --list | grep postgresql ii pgdg-keyring 2018.2 all keyring for apt.postgresql.org ii postgresql 12+215.pgdg18.04+1 all object-relational SQL database (supported version) ii postgresql-12 12.3-1.pgdg18.04+1 amd64 object-relational SQL database, version 12 server ii postgresql-client-12 12.3-1.pgdg18.04+1 amd64 front-end programs for PostgreSQL 12 ii postgresql-client-common 215.pgdg18.04+1 all manager for multiple PostgreSQL client versions ii postgresql-common 215.pgdg18.04+1 all PostgreSQL database-cluster manager #查看postgres安装版本（psql --version） zhangyanfeng@ubuntu:/etc/apt/sources.list.d$ psql --version psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

5.安装pgadmin3

#sudo apt-get install pgadmin3 zhangyanfeng@ubuntu:/etc/postgresql/12/main$ sudo apt-get install pgadmin3 [sudo] password for zhangyanfeng: Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: linux-headers-4.15.0-101 linux-headers-4.15.0-101-generic linux-image-4.15.0-101-generic linux-modules-4.15.0-101-generic linux-modules-extra-4.15.0-101-generic Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: libjs-underscore libwxbase3.0-0v5 libwxgtk3.0-0v5 pgadmin3-data Suggested packages: pgagent postgresql-contrib The following NEW packages will be installed: libjs-underscore libwxbase3.0-0v5 libwxgtk3.0-0v5 pgadmin3 pgadmin3-data 0 upgraded, 5 newly installed, 0 to remove and 389 not upgraded. Need to get 10.9 MB of archives. After this operation, 42.4 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Get:1 http://us.archive.ubuntu.com/ubuntu bionic/main amd64 libjs-underscore all 1.8.3~dfsg-1 [59.9 kB] Get:2 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 pgadmin3-data all 1.22.2-6.pgdg18.04+2 [2,514 kB] Get:1 http://us.archive.ubuntu.com/ubuntu bionic/main amd64 libjs-underscore all 1.8.3~dfsg-1 [59.9 kB] Get:3 http://us.archive.ubuntu.com/ubuntu bionic/universe amd64 libwxbase3.0-0v5 amd64 3.0.4+dfsg-3 [954 kB] Get:3 http://us.archive.ubuntu.com/ubuntu bionic/universe amd64 libwxbase3.0-0v5 amd64 3.0.4+dfsg-3 [954 kB] Get:4 http://us.archive.ubuntu.com/ubuntu bionic/universe amd64 libwxgtk3.0-0v5 amd64 3.0.4+dfsg-3 [4,182 kB] Get:5 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 pgadmin3 amd64 1.22.2-6.pgdg18.04+2 [3,193 kB] Fetched 10.4 MB in 7min 41s (22.6 kB/s) r Selecting previously unselected package libjs-underscore. (Reading database ... 254741 files and directories currently installed.) Preparing to unpack .../libjs-underscore_1.8.3~dfsg-1_all.deb ... Unpacking libjs-underscore (1.8.3~dfsg-1) ... Selecting previously unselected package libwxbase3.0-0v5:amd64. Preparing to unpack .../libwxbase3.0-0v5_3.0.4+dfsg-3_amd64.deb ... Unpacking libwxbase3.0-0v5:amd64 (3.0.4+dfsg-3) ... Selecting previously unselected package libwxgtk3.0-0v5:amd64. Preparing to unpack .../libwxgtk3.0-0v5_3.0.4+dfsg-3_amd64.deb ... Unpacking libwxgtk3.0-0v5:amd64 (3.0.4+dfsg-3) ... Selecting previously unselected package pgadmin3-data. Preparing to unpack .../pgadmin3-data_1.22.2-6.pgdg18.04+2_all.deb ... Unpacking pgadmin3-data (1.22.2-6.pgdg18.04+2) ... Selecting previously unselected package pgadmin3. Preparing to unpack .../pgadmin3_1.22.2-6.pgdg18.04+2_amd64.deb ... Unpacking pgadmin3 (1.22.2-6.pgdg18.04+2) ... Setting up libjs-underscore (1.8.3~dfsg-1) ... Processing triggers for mime-support (3.60ubuntu1) ... Processing triggers for desktop-file-utils (0.23-1ubuntu3) ... Setting up pgadmin3-data (1.22.2-6.pgdg18.04+2) ... Setting up libwxbase3.0-0v5:amd64 (3.0.4+dfsg-3) ... Processing triggers for libc-bin (2.27-3ubuntu1) ... Processing triggers for man-db (2.8.3-2) ... Processing triggers for gnome-menus (3.13.3-11ubuntu1) ... Setting up libwxgtk3.0-0v5:amd64 (3.0.4+dfsg-3) ... Setting up pgadmin3 (1.22.2-6.pgdg18.04+2) ... Processing triggers for libc-bin (2.27-3ubuntu1) ...

6.查看所有用户名

7.创建用户

#切换到postgres zhangyanfeng@ubuntu:~$ su postgres Password: postgres@ubuntu:/home/zhangyanfeng$ psql psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1)) Type "help" for help. postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zyf | Create role, Create DB | {} zyfpostgres | Create role, Create DB | {} #创建用户 postgres=# create user zyftest with password '123456'; CREATE ROLE postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zyf | Create role, Create DB | {} zyfpostgres | Create role, Create DB | {} zyftest | | {} postgres=#

8.删除用户

#切换到postgres zhangyanfeng@ubuntu:~$ su postgres Password: postgres@ubuntu:/home/zhangyanfeng$ psql psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1)) Type "help" for help. postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zyf | Create role, Create DB | {} zyfpostgres | Create role, Create DB | {} zyftest | | {} #删除用户 postgres=# drop role zyftest; DROP ROLE postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zyf | Create role, Create DB | {} zyfpostgres | Create role, Create DB | {}

9.创建或删除数据库

#创建数据库 postgres=# create database zyfpostgres owner zyfpostgres; CREATE DATABASE postgres=# #分配数据库权限 postgres=# grant all on database zyfpostgres to zyfpostgres ; GRANT postgres=# #删除数据库

10.退出psql

#退出psql postgres=# \q postgres@ubuntu:/home/zhangyanfeng$

11.创建普通用户与数据库同名

#创建普通用户与数据库同名 zhangyanfeng@ubuntu:~$ sudo adduser zyfpostgres [sudo] password for zhangyanfeng: Adding user `zyfpostgres' ... Adding new group `zyfpostgres' (1001) ... Adding new user `zyfpostgres' (1001) with group `zyfpostgres' ... Creating home directory `/home/zyfpostgres' ... Copying files from `/etc/skel' ... Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully Changing the user information for zyfpostgres Enter the new value, or press ENTER for the default Full Name []: Room Number []: Work Phone []: Home Phone []: Other []: Is the information correct? [Y/n] y zhangyanfeng@ubuntu:~$ su zyfpostgres Password: zyfpostgres@ubuntu:/home/zhangyanfeng$

12.以用户名的身份连接数据库

#以zyfpostgres的身份连接数据库zyfpostgres zyfpostgres@ubuntu:~$ psql -d zyfpostgres psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1)) Type "help" for help. zyfpostgres=>

Processed: 0.017, SQL: 9