1.下载自己需要的二进制postgres数据库,下载地址(以10.14版本为例):
https://www.enterprisedb.com/download-postgresql-binaries或者
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
2.步骤:
(1)创建普通用户postgres
useradd postgres;
(2)创建数据及日志目录,并做相应授权:
mkdir -p /usr/local/postgresql/pgsql/{data,log}
chown -R postgres:postgres /usr/local/postgresql/pgsql
(3)进行数据库初始化:
切换postgres用户,执行初始化操作
su postgres
cd /usr/local/postgresql/pgsql/bin/
./initdb -E utf8 -D /usr/local/postgresql/pgsql/data
初始化完成提示为:
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 locale "zh_CN.utf8". initdb: could not find suitable text search configuration for locale "zh_CN.utf8" The default text search configuration will be set to "simple". Data page checksums are disabled. fixing permissions on existing directory /home/SQL/PostgreSQL/Data/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: ./pg_ctl -D /usr/local/postgresql/pgsql/data -l logfile start
(4)启动数据库
./pg_ctl -D /usr/local/postgresql/pgsql/data -l /usr/local/postgresql/pgsql/log/postgres.log start
配置开机自启动参考:
https://www.postgresql.org/docs/10/server-start.html
部分截图如下:
(5)登录数据库,并创建数据库和用户
./psql
create user admin with password 'admin';
create user postgres with password 'postgres';
create database sonarqube with encoding='utf8' owner=postgres;
验证登录:
./psql -U postgres -d sonarqube
登录指定数据库后创建表my_table:
CREATE TABLE my_table(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( 一个或多个列 )
);
查看表详细信息: d my_table;
查看当前数据库列表(在任何数据库都可使用):
l
退出交互式界面;
q
关闭数据库服务:
./pg_ctl -D /usr/local/postgresql/pgsql/data stop
连接远程数据库:
-h参数指定服务器地址,默认为127.0.0.1 -d指定连接之后选中的数据库,默认也是postgres -U指定用户,默认是当前用户 -p 指定端口号,默认是"5432" 其它更多的参数选项可以执行:./psql --help 查看 {pgsql安装目录}/bin/psql -h {服务器IP} -d postgres -U postgres -p 5432 如: ./psql -h 127.0.0.1 -d mydb -U postgres -p 5432