安装postgresql 数据库

news/2024/7/9 20:12:01 标签: 数据库, postgresql

postgresql 官方地址  PostgreSQL: The world's most advanced open source database

本次服务器环境为centos8 stream ,因此，选择centos8版本进行安装

1.安装

a.安装操作，本次安装的是最新14版本的数据库

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server

b.修改数据库默认存储

# 创建postgresql 数据目录，并授权postgres用户

mkdir -pv /data/pgsql/14/data/
chown postgres.postgres /data/pgsql/14/data/
# 修改postgresql 服务文件，并修改pgdata
vim /usr/lib/systemd/system/postgresql-14.service


# 修改PGDATA
Environment=PGDATA=/data/pgsql/14/data/
# 修改service之后，需要daemon-reload

sudo systemctl daemon-reload

c.初始化数据，并启动服务

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

2.创建数据库和用户并授权

su - postgres

psql
CREATE USER jiradbuser WITH ENCRYPTED PASSWORD 'jiradbpwd..';
CREATE DATABASE jiradb WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
grant all privileges on database jiradb to jiradbuser;

 3.连接测试

 psql -h 127.0.0.1 -U jiradbuser -W -d jiradb

 附录：数据库操作指令帮助

[root@localhost data]# su - postgres
Last login: Sun Aug 21 22:52:15 EDT 2022 on pts/0
[postgres@localhost ~]$ psql 
psql (14.5)
Type "help" for help.

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 [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [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 commands

Query 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 file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [-n] [STRING]    write string to standard output (-n for no newline)
  \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 [-n] [STRING]   write string to \o output stream (-n for no newline)
  \warn [-n] [STRING]    write string to standard error (-n for no newline)

Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block

Informational
  (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
  \dAc[+] [AMPTRN [TYPEPTRN]]  list operator classes
  \dAf[+] [AMPTRN [TYPEPTRN]]  list operator families
  \dAo[+] [AMPTRN [OPFPTRN]]   list operators of operator families
  \dAp[+] [AMPTRN [OPFPTRN]]   list support functions of operator families
  \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
  \des[+] [PATTERN]      list foreign servers
  \det[+] [PATTERN]      list foreign tables
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
                         list [only agg/normal/procedure/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
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
                         list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]
  \drds [ROLEPTRN [DBPTRN]] 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
  \dX     [PATTERN]      list extended statistics
  \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 \dp

Formatting
  \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
                         (border|columns|csv_fieldsep|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 user

Operating 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 shell

Variables
  \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 variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations


http://www.niftyadmin.cn/n/1265362.html

相关文章

nginx主机黑白名单[geoip]

nginx主机黑白名单[geoip]

国家黑白名单通过 ngx_http_geoip2_module 模块实现 1.下载 libmaxminddb 并编译安装 wget https://github.com/maxmind/libmaxminddb/releases/download/1.4.3/libmaxminddb-1.4.3.tar.gz tar xvf libmaxminddb-1.4.3.tar.gz cd libmaxminddb-1.4.3/ ./configure make make …
阅读更多...
阿里云ECS vpc网络与办公网内网互通

阿里云ECS vpc网络与办公网内网互通

公网远程操作很不安全&#xff0c;因此决定使用内网互通模式&#xff0c;虽然阿里云有自带的vpn&#xff0c;但是太贵了&#xff0c;身为运维的我&#xff0c;一定要为公司省钱&#xff0c;只能想想法子&#xff0c;自己搭建一套内网vpn使用 环境&#xff1a; 阿里云vpc网络的e…
阅读更多...
项目管理之jira 9.1安装

项目管理之jira 9.1安装

系统环境centos8 官方文档 Installing Jira applications on Linux | Administering Jira applications Data Center and Server 9.1 | Atlassian Documentation 1.下载程序包 a,访问下载连接&#xff0c;进行下载最新的安装包 更新 Jira Software Server | Atlassian # 软件…
阅读更多...
frp使用oidc认证和搭建

frp使用oidc认证和搭建

frp 是什么&#xff1f; frp 是一个专注于内网穿透的高性能的反向代理应用&#xff0c;支持 TCP、UDP、HTTP、HTTPS 等多种协议。可以将内网服务以安全、便捷的方式通过具有公网 IP 节点的中转暴露到公网 github源码&#xff1a;https://github.com/fatedier/frp/ 官方文档&…
阅读更多...
容器化部署mariadb数据库

容器化部署mariadb数据库

compose文档&#xff1a;Compose specification | Docker Documentation 容器地址及文档&#xff1a;Docker Hub 部署环境依赖 1.docker-ce安装 a.卸载老版本的docker sudo yum remove docker \docker-client \docker-client-latest \docker-common \docker-latest \docker-l…
阅读更多...
kms服务器搭建及测试

kms服务器搭建及测试

vlmcsd代码仓库&#xff1a;https://github.com/Wind4/vlmcsd 搭建步骤 本次操作环境centos7&#xff0c;通过内网进行搭建 1.安装依赖环境 yum install gcc git make -y 2.下载源码&#xff0c;编译安装 cd /var/opt/ git clone https://github.com/Wind4/vlmcsd.git cd vlmcs…
阅读更多...
知识库协同confluence7.19安装

知识库协同confluence7.19安装

系统环境centos8 官方文档&#xff1a;Installing Confluence | Confluence Data Center and Server 7.19 | Atlassian Documentation docker安装&#xff1a;Docker Hub 本次是通过压缩包安装 1.下载程序包 a,访问下载连接&#xff0c;进行下载最新的安装包 Confluence Serv…
阅读更多...
Django 增加自定义加密字段

Django 增加自定义加密字段

前言 由于业务需求&#xff0c;并且保证数据安全行&#xff0c;某些数据字段需要加密&#xff0c;比如用户身份等隐私数据。 数据加密情况下&#xff0c;假设被脱库&#xff0c;也不会丢失用户隐私数据 实现 AES的优点是比较快&#xff0c;缺点就是密钥的保密很关键&#xff0…
阅读更多...
最新文章

Copyright @ 2022~2023