当前位置: 代码迷 >> 综合 >> How to install Postgresql 10 in Linux Mint 18
  详细解决方案

How to install Postgresql 10 in Linux Mint 18

热度:92   发布时间:2023-12-15 00:38:41.0

How to install Postgresql 10 in Linux Mint 18

    • Overview
    • Add soures
    • Update apt cache
    • Install
      • Configuration
        • Account
        • Enable remote access
    • pgadmin4
    • PSQL
    • Reference

Overview

??PostgreSQL数据库是一个高性能的全功能的开源关系型数据库,这里讲解一下如何在Linux mint 18.x 下安装 PostgreSQL 10,考虑到Linux mint 18.x是基于Ubuntu 16.04 LTS构建的,所以本文也完全适用于Ubuntu 16.04 LTS。

Add soures

wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update apt cache

apt update

Install

??查找postgresql10安装包

apt install postgresql-10 pgadmin4 -y

Configuration

Account

??PostgreSQL 10安装成功以后,通常情况下需要能进行远程管理,因为这里我们配置一下,使其能从远程访问。另外,PostgreSQL10安装成功以后,系统默认创建了一个名为postgres操作系统用户作为数据库用户,但是该用户密码为空,即没有设置,这里我们也要给该用户设置一个密码。

??我们将通过以下命令设置PostgreSQL 10的数据库用户postgres的用户密码:

lwk@qwfys ~ $ sudo -u postgres psql
[sudo] password for lwk: 
psql (10.4 (Ubuntu 10.4-1.pgdg16.04+1))
Type "help" for help.postgres=# 
postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=# \q
lwk@qwfys ~ $ 

??接着,我们将通过以下命令对数据库用户postgres对应的操作系统用户设置密码:

lwk@qwfys ~ $ sudo passwd postgres 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
lwk@qwfys ~

??密码设置完成。

Enable remote access

??postgresql10配置文件在目录/etc/postgresql/10/main/中,内容如下:

lwk@qwfys ~ $ ll /etc/postgresql/10/main/
total 60
drwxr-xr-x 3 postgres postgres  4096 May 15 05:00 ./
drwxr-xr-x 3 postgres postgres  4096 May 15 04:22 ../
drwxr-xr-x 2 postgres postgres  4096 May 15 04:22 conf.d/
-rw-r--r-- 1 postgres postgres   315 May 15 04:22 environment
-rw-r--r-- 1 postgres postgres   143 May 15 04:22 pg_ctl.conf
-rw-r----- 1 postgres postgres  4686 May 15 04:22 pg_hba.conf
-rw-r----- 1 postgres postgres  1636 May 15 04:22 pg_ident.conf
-rw-r--r-- 1 postgres postgres 23001 May 15 04:59 postgresql.conf
-rw-r--r-- 1 postgres postgres   317 May 15 04:22 start.conf 
lwk@qwfys ~

??远程访问将涉及postgresql.conf和pg_hba.conf两个文件,文件postgresql.conf内容如下:

lwk@qwfys ~ $ cat /etc/postgresql/10/main/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()". Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# TB = terabytes h = hours
# d = days#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.data_directory = '/var/lib/postgresql/10/main'		# use data in another directory# (change requires restart)
hba_file = '/etc/postgresql/10/main/pg_hba.conf'	# host-based authentication file# (change requires restart)
ident_file = '/etc/postgresql/10/main/pg_ident.conf'	# ident configuration file# (change requires restart)# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/10-main.pid'			# write an extra PID file# (change requires restart)#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------# - Connection Settings -#listen_addresses = 'localhost' # what IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost'; use '*' for all# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 100			# (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql'	# comma-separated list of directories# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation# (change requires restart)
#bonjour = off # advertise server via Bonjour# (change requires restart)
#bonjour_name = '' # defaults to the computer name# (change requires restart)# - Security and Authentication -#authentication_timeout = 1min # 1s-600s
ssl = on
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_dh_params_file = ''
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
#password_encryption = md5 # md5 or scram-sha-256
#db_user_namespace = off
#row_security = on# GSSAPI using Kerberos
#krb_server_keyfile = ''
#krb_caseins_users = off# - TCP Keepalives -
# see "man 7 tcp" for details#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;# 0 selects the system default#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------# - Memory -shared_buffers = 128MB			# min 128kB# (change requires restart)
#huge_pages = try # on, off, or try# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#replacement_sort_tuples = 150000 # limits use of replacement selection sort
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB # min 100kB
dynamic_shared_memory_type = posix	# the default is the first option# supported by the operating system:# posix# sysv# windows# mmap# use none to disable dynamic shared memory# (change requires restart)# - Disk -#temp_file_limit = -1 # limits per-process temp file space# in kB, or -1 for no limit# - Kernel Resource Usage -#max_files_per_process = 1000 # min 25# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)# - Cost-Based Vacuum Delay -#vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits# - Background Writer -#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB # measured in pages, 0 disables# - Asynchronous Behavior -#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#max_worker_processes = 8 # (change requires restart)
#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
#max_parallel_workers = 8 # maximum number of max_worker_processes that# can be used in parallel queries
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate# (change requires restart)
#backend_flush_after = 0 # measured in pages, 0 disables#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------# - Settings -#wal_level = replica # minimal, replica, or logical# (change requires restart)
#fsync = on # flush data to disk for crash safety# (turning this off can cause# unrecoverable data corruption)
#synchronous_commit = on # synchronization level;# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync # the default is the first option# supported by the operating system:# open_datasync# fdatasync (default on Linux)# fsync# fsync_writethrough# open_sync
#full_page_writes = on # recover from partial page writes
#wal_compression = off # enable compression of full-page writes
#wal_log_hints = off # also do full page writes of non-critical updates# (change requires restart)
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after = 1MB # measured in pages, 0 disables#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000# - Checkpoints -#checkpoint_timeout = 5min # range 30s-1d
#max_wal_size = 1GB
#min_wal_size = 80MB
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables# - Archiving -#archive_mode = off # enables archiving; off, on, or always# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment# placeholders: %p = path of file to archive# %f = file name only# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this# number of seconds; 0 disables#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------# - Sending Server(s) -# Set these on the master and on any standby that will send replication data.#max_wal_senders = 10 # max number of walsender processes# (change requires restart)
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables#max_replication_slots = 10 # max number of replication slots# (change requires restart)
#track_commit_timestamp = off # collect timestamp of transaction commit# (change requires restart)# - Master Server -# These settings are ignored on a standby server.#synchronous_standby_names = '' # standby servers that provide sync rep# method to choose sync standbys, number of sync standbys,# and comma-separated list of application_name# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed# - Standby Servers -# These settings are ignored on a master server.#hot_standby = on # "off" disallows queries during recovery# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries# when reading WAL from archive;# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries# when reading streaming WAL;# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often# 0 disables
#hot_standby_feedback = off # send info from standby to prevent# query conflicts
#wal_receiver_timeout = 60s # time that receiver waits for# communication from master# in milliseconds; 0 disables
#wal_retrieve_retry_interval = 5s # time to wait before retrying to# retrieve WAL after a failed attempt# - Subscribers -# These settings are ignored on a publisher.#max_logical_replication_workers = 4 # taken from max_worker_processes# (change requires restart)
#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------# - Planner Method Configuration -#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on# - Planner Cost Constants -#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB# - Genetic Query Optimizer -#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0# - Other Planner Options -#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit# JOIN clauses
#force_parallel_mode = off#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------# - Where to Log -#log_destination = 'stderr' # Valid values are combinations of# stderr, csvlog, syslog, and eventlog,# depending on platform. csvlog# requires logging_collector to be on.# This is used when logging to stderr:
#logging_collector = off # Enable capturing of stderr and csvlog# into log files. Required to be on for# csvlogs.# (change requires restart)# These are only used if logging_collector is on:
#log_directory = 'log' # directory where log files are written,# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,# begin with 0 to use octal notation
#log_truncate_on_rotation = off # If on, an existing log file with the# same name as the new log file will be# truncated rather than appended to.# But such truncation only occurs on# time-driven rotation, not on restarts# or size-driven rotation. Default is# off, meaning append to existing files# in all cases.
#log_rotation_age = 1d # Automatic rotation of logfiles will# happen after that time. 0 disables.
#log_rotation_size = 10MB # Automatic rotation of logfiles will# happen after that much log output.# 0 disables.# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on# This is only relevant when logging to eventlog (win32):
# (change requires restart)
#event_source = 'PostgreSQL'# - When to Log -#client_min_messages = notice # values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# log# notice# warning# error#log_min_messages = warning # values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# info# notice# warning# error# log# fatal# panic#log_min_error_statement = error # values in order of decreasing detail:# debug5# debug4# debug3# debug2# debug1# info# notice# warning# error# log# fatal# panic (effectively off)#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements# and their durations, > 0 logs only# statements running at least this number# of milliseconds# - What to Log -#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%m [%p] %q%u@%d '		# special values:# %a = application name# %u = user name# %d = database name# %r = remote host and port# %h = remote host# %p = process ID# %t = timestamp without milliseconds# %m = timestamp with milliseconds# %n = timestamp with milliseconds (as a Unix epoch)# %i = command tag# %e = SQL state# %c = session ID# %l = session line number# %s = session start timestamp# %v = virtual transaction ID# %x = transaction ID (0 if none)# %q = stop here in non-session# processes# %% = '%'# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1 # log temporary files equal or larger# than the specified size in kilobytes;# -1 disables, 0 logs all temp files
log_timezone = 'PRC'# - Process Title -cluster_name = '10/main'			# added to process titles if nonempty# (change requires restart)
#update_process_title = on#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------# - Query/Index Statistics Collector -#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
stats_temp_directory = '/var/run/postgresql/10-main.pg_stat_tmp'# - Statistics Monitoring -#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------#autovacuum = on # Enable autovacuum subprocess? 'on'# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and# their durations, > 0 logs only# actions running at least this number# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age# before forced vacuum# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for# autovacuum, in milliseconds;# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for# autovacuum, -1 means use# vacuum_cost_limit#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------# - Statement Behavior -#search_path = '"$user", public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#lock_timeout = 0 # in milliseconds, 0 is disabled
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
#gin_pending_list_limit = 4MB# - Locale and Formatting -datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default' # Select the set of available time zone# abbreviations. Currently, there are# Default# Australia (historical usage)# India# You can create your own file in# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database# encoding# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'			# locale for system error message# strings
lc_monetary = 'zh_CN.UTF-8'			# locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'			# locale for number formatting
lc_time = 'en_US.UTF-8'				# locale for time formatting# default configuration for text search
default_text_search_config = 'pg_catalog.english'# - Other Defaults -#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10# (change requires restart)
#max_pred_locks_per_transaction = 64 # min 10# (change requires restart)
#max_pred_locks_per_relation = -2 # negative values mean# (max_pred_locks_per_transaction# / -max_pred_locks_per_relation) - 1
#max_pred_locks_per_page = 2 # min 0#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------# - Previous PostgreSQL Versions -#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#operator_precedence_warning = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on# - Other Platforms and Clients -#transform_null_equals = off#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------# These options allow settings to be loaded from files other than the
# default postgresql.conf.include_dir = 'conf.d'			# include files ending in '.conf' from# directory 'conf.d'
#include_if_exists = 'exists.conf' # include file only if it exists
#include = 'special.conf' # include file#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------# Add settings for extensions here
lwk@qwfys ~ $ 

??这里,我们先将

#listen_addresses = 'localhost' # what IP address(es) to listen on;

??改为

 listen_addresses = '*'         # what IP address(es) to listen on;

??接着将

#password_encryption = md5 # md5 or scram-sha-256

??改为

password_encryption = on		# md5 or scram-sha-256

??之后,我们先来浏览器一下文件pg_hba.conf内容,具体如下:

lwk@qwfys ~ $ sudo cat /etc/postgresql/10/main/pg_hba.conf 
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches. It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask. A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts. Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE. The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted. Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the server receives a
# SIGHUP signal. If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
lwk@qwfys ~ $ 

??为了完成我们的任务,我们在文件末尾追加如下内容:

host    all             all             0.0.0.0 0.0.0.0         md5

??为了从远程主机进行访问该数据库实例,我们需要打开防火墙,将端口5432开放给外面的主机,可以通过以下命令完成防火墙的设置。

lwk@qwfys ~ $ sudo ufw allow 5432
Rule added
Rule added (v6)
lwk@qwfys ~ $ sudo ufw status
Status: activeTo                         Action      From
--                         ------      ----
5432                       ALLOW       Anywhere                  
5432 (v6)                  ALLOW       Anywhere (v6)             lwk@qwfys ~ $ 

?? 接下来,我们重启PostgreSQL 10数据库服务

lwk@qwfys ~ $ sudo systemctl restart postgresql
lwk@qwfys ~ $ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMSLoaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)Active: active (exited) since Tue 2018-05-15 05:31:27 CST; 3s agoProcess: 25067 ExecStart=/bin/true (code=exited, status=0/SUCCESS)Main PID: 25067 (code=exited, status=0/SUCCESS)May 15 05:31:27 qwfys systemd[1]: Starting PostgreSQL RDBMS...
May 15 05:31:27 qwfys systemd[1]: Started PostgreSQL RDBMS.
lwk@qwfys ~ $ 

??我们看到数据库PostgreSQL实例启动成功,接下来,我们就可以通过客户端来管理该数据库系统了。PostgreSQL数据库客户端很多,这里主要讲一下psql和pgadmin4客户端,前者是一个命令行客户端,后者是一个基于Web的客户端。

pgadmin4

??安装完成pgadmin4以后,系统菜单就会有pgadmin4菜单项,下面几幅图是我在Linux mint 18.3系统上的截图,因为Linux mint 18.x是基于Ubuntu 16.04 LTS构建的,所以,对于Ubuntu Desktop 16.04 LTS也是类似的,因为桌面环境不同,显示风格不太一致,但是功能是类似的。

这里写图片描述

这里写图片描述

这里写图片描述

这里写图片描述

这里写图片描述

这里写图片描述

PSQL

lwk@qwfys ~ $ sudo -u postgres psql
[sudo] password for lwk: 
psql (10.4 (Ubuntu 10.4-1.pgdg16.04+1))
Type "help" for help.postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms\h for help with SQL commands\? for help with psql commands\g or terminate with semicolon to execute query\q to quit
postgres=# \h
Available help:ABORT                            ALTER SERVER                     CREATE CAST                      CREATE TABLE                     DROP EXTENSION                   DROP TEXT SEARCH TEMPLATE        REVOKEALTER AGGREGATE                  ALTER STATISTICS                 CREATE COLLATION                 CREATE TABLE AS                  DROP FOREIGN DATA WRAPPER        DROP TRANSFORM                   ROLLBACKALTER COLLATION                  ALTER SUBSCRIPTION               CREATE CONVERSION                CREATE TABLESPACE                DROP FOREIGN TABLE               DROP TRIGGER                     ROLLBACK PREPAREDALTER CONVERSION                 ALTER SYSTEM                     CREATE DATABASE                  CREATE TEXT SEARCH CONFIGURATION DROP FUNCTION                    DROP TYPE                        ROLLBACK TO SAVEPOINTALTER DATABASE                   ALTER TABLE                      CREATE DOMAIN                    CREATE TEXT SEARCH DICTIONARY    DROP GROUP                       DROP USER                        SAVEPOINTALTER DEFAULT PRIVILEGES         ALTER TABLESPACE                 CREATE EVENT TRIGGER             CREATE TEXT SEARCH PARSER        DROP INDEX                       DROP USER MAPPING                SECURITY LABELALTER DOMAIN                     ALTER TEXT SEARCH CONFIGURATION  CREATE EXTENSION                 CREATE TEXT SEARCH TEMPLATE      DROP LANGUAGE                    DROP VIEW                        SELECTALTER EVENT TRIGGER              ALTER TEXT SEARCH DICTIONARY     CREATE FOREIGN DATA WRAPPER      CREATE TRANSFORM                 DROP MATERIALIZED VIEW           END                              SELECT INTOALTER EXTENSION                  ALTER TEXT SEARCH PARSER         CREATE FOREIGN TABLE             CREATE TRIGGER                   DROP OPERATOR                    EXECUTE                          SETALTER FOREIGN DATA WRAPPER       ALTER TEXT SEARCH TEMPLATE       CREATE FUNCTION                  CREATE TYPE                      DROP OPERATOR CLASS              EXPLAIN                          SET CONSTRAINTSALTER FOREIGN TABLE              ALTER TRIGGER                    CREATE GROUP                     CREATE USER                      DROP OPERATOR FAMILY             FETCH                            SET ROLEALTER FUNCTION                   ALTER TYPE                       CREATE INDEX                     CREATE USER MAPPING              DROP OWNED                       GRANT                            SET SESSION AUTHORIZATIONALTER GROUP                      ALTER USER                       CREATE LANGUAGE                  CREATE VIEW                      DROP POLICY                      IMPORT FOREIGN SCHEMA            SET TRANSACTIONALTER INDEX                      ALTER USER MAPPING               CREATE MATERIALIZED VIEW         DEALLOCATE                       DROP PUBLICATION                 INSERT                           SHOWALTER LANGUAGE                   ALTER VIEW                       CREATE OPERATOR                  DECLARE                          DROP ROLE                        LISTEN                           START TRANSACTIONALTER LARGE OBJECT               ANALYZE                          CREATE OPERATOR CLASS            DELETE                           DROP RULE                        LOAD                             TABLEALTER MATERIALIZED VIEW          BEGIN                            CREATE OPERATOR FAMILY           DISCARD                          DROP SCHEMA                      LOCK                             TRUNCATEALTER OPERATOR                   CHECKPOINT                       CREATE POLICY                    DO                               DROP SEQUENCE                    MOVE                             UNLISTENALTER OPERATOR CLASS             CLOSE                            CREATE PUBLICATION               DROP ACCESS METHOD               DROP SERVER                      NOTIFY                           UPDATEALTER OPERATOR FAMILY            CLUSTER                          CREATE ROLE                      DROP AGGREGATE                   DROP STATISTICS                  PREPARE                          VACUUMALTER POLICY                     COMMENT                          CREATE RULE                      DROP CAST                        DROP SUBSCRIPTION                PREPARE TRANSACTION              VALUESALTER PUBLICATION                COMMIT                           CREATE SCHEMA                    DROP COLLATION                   DROP TABLE                       REASSIGN OWNED                   WITHALTER ROLE                       COMMIT PREPARED                  CREATE SEQUENCE                  DROP CONVERSION                  DROP TABLESPACE                  REFRESH MATERIALIZED VIEW        ALTER RULE                       COPY                             CREATE SERVER                    DROP DATABASE                    DROP TEXT SEARCH CONFIGURATION   REINDEX                          ALTER SCHEMA                     CREATE ACCESS METHOD             CREATE STATISTICS                DROP DOMAIN                      DROP TEXT SEARCH DICTIONARY      RELEASE SAVEPOINT                ALTER SEQUENCE                   CREATE AGGREGATE                 CREATE SUBSCRIPTION              DROP EVENT TRIGGER               DROP TEXT SEARCH PARSER          RESET                            
postgres=#\?
General\copyright             show PostgreSQL usage and distribution terms\crosstabview [COLUMNS] execute query and display results in crosstab\errverbose            show most recent error message at maximum verbosity\g [FILE] or ;         execute query (and send results to file or |pipe)\gexec                 execute query, then execute each value in its result\gset [PREFIX]         execute query and store results in psql variables\gx [FILE]             as \g, but forces expanded output mode\q                     quit psql\watch [SEC]           execute query every SEC secondsHelp\? [commands]          show help on backslash commands\? options             show help on psql command-line options\? variables           show help on special variables\h [NAME]              help on syntax of SQL commands, * for all commandsQuery Buffer\e [FILE] [LINE]       edit the query buffer (or file) with external editor\ef [FUNCNAME [LINE]]  edit function definition with external editor\ev [VIEWNAME [LINE]]  edit view definition with external editor\p                     show the contents of the query buffer\r                     reset (clear) the query buffer\s [FILE]              display history or save it to file\w FILE                write query buffer to fileInput/Output\copy ...              perform SQL COPY with data stream to the client host\echo [STRING]         write string to standard output\i FILE                execute commands from file\ir FILE               as \i, but relative to location of current script\o [FILE]              send all query results to file or |pipe\qecho [STRING]        write string to query output stream (see \o)Conditional\if EXPR               begin conditional block\elif EXPR             alternative within current conditional block\else                  final alternative within current conditional block\endif                 end conditional blockInformational(options: S = show system objects, + = additional detail)\d[S+]                 list tables, views, and sequences\d[S+]  NAME           describe table, view, sequence, or index\da[S]  [PATTERN]      list aggregates\dA[+]  [PATTERN]      list access methods\db[+]  [PATTERN]      list tablespaces\dc[S+] [PATTERN]      list conversions\dC[+]  [PATTERN]      list casts\dd[S]  [PATTERN]      show object descriptions not displayed elsewhere\dD[S+] [PATTERN]      list domains\ddp    [PATTERN]      list default privileges\dE[S+] [PATTERN]      list foreign tables\det[+] [PATTERN]      list foreign tables\des[+] [PATTERN]      list foreign servers\deu[+] [PATTERN]      list user mappings\dew[+] [PATTERN]      list foreign-data wrappers\df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions\dF[+]  [PATTERN]      list text search configurations\dFd[+] [PATTERN]      list text search dictionaries\dFp[+] [PATTERN]      list text search parsers\dFt[+] [PATTERN]      list text search templates\dg[S+] [PATTERN]      list roles\di[S+] [PATTERN]      list indexes\dl                    list large objects, same as \lo_list\dFd[+] [PATTERN]      list text search dictionaries\dFp[+] [PATTERN]      list text search parsers\dFt[+] [PATTERN]      list text search templates\dg[S+] [PATTERN]      list roles\di[S+] [PATTERN]      list indexes\dl                    list large objects, same as \lo_list\dL[S+] [PATTERN]      list procedural languages\dm[S+] [PATTERN]      list materialized views\dn[S+] [PATTERN]      list schemas\do[S]  [PATTERN]      list operators\dO[S+] [PATTERN]      list collations\dp     [PATTERN]      list table, view, and sequence access privileges\drds [PATRN1 [PATRN2]] list per-database role settings\dRp[+] [PATTERN]      list replication publications\dRs[+] [PATTERN]      list replication subscriptions\ds[S+] [PATTERN]      list sequences\dt[S+] [PATTERN]      list tables\dT[S+] [PATTERN]      list data types\du[S+] [PATTERN]      list roles\dv[S+] [PATTERN]      list views\dx[+]  [PATTERN]      list extensions\dy     [PATTERN]      list event triggers\l[+]   [PATTERN]      list databases\sf[+]  FUNCNAME       show a function's definition\sv[+] VIEWNAME show a view's definition\z      [PATTERN]      same as \dpFormatting\a                     toggle between unaligned and aligned output mode\C [STRING]            set table title, or unset if none\f [STRING]            show or set field separator for unaligned query output\H                     toggle HTML output mode (currently off)\pset [NAME [VALUE]]   set table output option(NAME := {
    border|columns|expanded|fieldsep|fieldsep_zero|footer|format|linestyle|null|numericlocale|pager|pager_min_lines|recordsep|recordsep_zero|tableattr|title|tuples_only|unicode_border_linestyle|unicode_column_linestyle|unicode_header_linestyle})\t [on|off]            show only rows (currently off)\T [STRING]            set HTML <table> tag attributes, or unset if none\x [on|off|auto]       toggle expanded output (currently off)Connection\c[onnect] {
    [DBNAME|- USER|- HOST|- PORT|-] | conninfo}connect to new database (currently "postgres")\conninfo              display information about current connection\encoding [ENCODING]   show or set client encoding\password [USERNAME]   securely change the password for a userOperating System\cd [DIR]              change the current working directory\setenv NAME [VALUE]   set or unset environment variable\timing [on|off]       toggle timing of commands (currently off)\! [COMMAND]           execute command in shell or start interactive shellVariables\prompt [TEXT] NAME    prompt user to set internal variable\set [NAME [VALUE]]    set internal variable, or list all if no parameters\unset NAME            unset (delete) internal variableLarge Objects\lo_export LOBOID FILE\lo_import FILE [COMMENT]\lo_list\lo_unlink LOBOID      large object operations
(END)

??这个时候按q就会退出上下文帮助

postgres=# 
postgres=# \q
lwk@qwfys ~ $ 

??通常项目开发过程中,我们会采用pgadmin4客户端完成应用软件的开发,对于运维人员,他们会采用psql完成相关的配置与管理。

Reference

  • PostgreSQL packages for Debian and Ubuntu
  • ubuntu 16.04 安装 PostgreSQL-9.6 及用pgadmin4连接测试