postgresql在centos上安装与配置
发布时间：2020-12-13 16:48:34 所属栏目：百科 来源：网络整理
导读：解压二进制包 建 /usr/postgresql目录 tar -xzvf postgresql-9.6.5-1-linux-x64-binaries.tar.gz ------------------------------------------------------------- (1)新增postgres用户组： groupadd postgres (2)新增postgres用户并且设置这个用户属于上面创
|
解压二进制包
建 /usr/postgresql目录 tar -xzvf postgresql-9.6.5-1-linux-x64-binaries.tar.gz ------------------------------------------------------------- (1)新增postgres用户组： groupadd postgres (2)新增postgres用户并且设置这个用户属于上面创建的postgres用户组： useradd -g postgres postgres (3)修改postgres用户密码:passwd postgres（这里设置密码为postgres） passwd postgres --修改postgres用户密码 -------------------------------------------------------- 授权 chown -R postgres:postgres /us r/postgresql/ -------------------------------------------------------- 初始化 切换用户 su postgres 切到解压的目录 初始化命令 bin/initdb -E utf8 -D /usr/postgresql/psql/data ------------------------------------------------------- [root@hserver1 pgsql]# su postgres [postgres@hserver1 pgsql]$ bin/initdb -E utf8 -D /usr/postgresql/psql/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.UTF-8". initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8" The default text search configuration will be set to "simple". Data page checksums are disabled. creating directory /usr/postgresql/psql/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: bin/pg_ctl -D /usr/postgresql/psql/data -l logfile start ------------------------------------------------------------------------ OK 看到这个表示已经初始化成功了。 启动postgresql 启动注意必须使用postgres用户启动 ./postgres -D /usr/postgresql/pgsql/data/ > /usr/postgresql/pgsql/data/log/postgres.log & pg_ctl -D /usr/postgresql/psql/data -l logfile start [postgres@hserver1 bin]$ ./pg_ctl -D /usr/postgresql/psql/data -l logfile start server starting ------------------------------------------------------------------------- 登录数据库 [postgres@hserver1 bin]$ ./psql psql.bin (9.6.5) 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 DEALLOCATE ALTER AGGREGATE DECLARE ALTER COLLATION DELETE ALTER CONVERSION DISCARD ALTER DATABASE DO ALTER DEFAULT PRIVILEGES DROP ACCESS METHOD ALTER DOMAIN DROP AGGREGATE ALTER EVENT TRIGGER DROP CAST ALTER EXTENSION DROP COLLATION ALTER FOREIGN DATA WRAPPER DROP CONVERSION ALTER FOREIGN TABLE DROP DATABASE ALTER FUNCTION DROP DOMAIN ALTER GROUP DROP EVENT TRIGGER ALTER INDEX DROP EXTENSION ALTER LANGUAGE DROP FOREIGN DATA WRAPPER ALTER LARGE OBJECT DROP FOREIGN TABLE ALTER MATERIALIZED VIEW DROP FUNCTION ALTER OPERATOR DROP GROUP ALTER OPERATOR CLASS DROP INDEX ALTER OPERATOR FAMILY DROP LANGUAGE ALTER POLICY DROP MATERIALIZED VIEW ALTER ROLE DROP OPERATOR ALTER RULE DROP OPERATOR CLASS ALTER SCHEMA DROP OPERATOR FAMILY ALTER SEQUENCE DROP OWNED ALTER SERVER DROP POLICY ALTER SYSTEM DROP ROLE ALTER TABLE DROP RULE ALTER TABLESPACE DROP SCHEMA ALTER TEXT SEARCH CONFIGURATION DROP SEQUENCE ALTER TEXT SEARCH DICTIONARY DROP SERVER ALTER TEXT SEARCH PARSER DROP TABLE ALTER TEXT SEARCH TEMPLATE DROP TABLESPACE ALTER TRIGGER DROP TEXT SEARCH CONFIGURATION ALTER TYPE DROP TEXT SEARCH DICTIONARY ALTER USER DROP TEXT SEARCH PARSER ALTER USER MAPPING DROP TEXT SEARCH TEMPLATE ALTER VIEW DROP TRANSFORM ANALYZE DROP TRIGGER BEGIN DROP TYPE CHECKPOINT DROP USER CLOSE DROP USER MAPPING CLUSTER DROP VIEW COMMENT END COMMIT EXECUTE COMMIT PREPARED EXPLAIN COPY FETCH CREATE ACCESS METHOD GRANT CREATE AGGREGATE IMPORT FOREIGN SCHEMA CREATE CAST INSERT CREATE COLLATION LISTEN CREATE CONVERSION LOAD CREATE DATABASE LOCK CREATE DOMAIN MOVE CREATE EVENT TRIGGER NOTIFY CREATE EXTENSION PREPARE CREATE FOREIGN DATA WRAPPER PREPARE TRANSACTION CREATE FOREIGN TABLE REASSIGN OWNED CREATE FUNCTION REFRESH MATERIALIZED VIEW CREATE GROUP REINDEX CREATE INDEX RELEASE SAVEPOINT CREATE LANGUAGE RESET CREATE MATERIALIZED VIEW REVOKE CREATE OPERATOR ROLLBACK CREATE OPERATOR CLASS ROLLBACK PREPARED CREATE OPERATOR FAMILY ROLLBACK TO SAVEPOINT CREATE POLICY SAVEPOINT CREATE ROLE SECURITY LABEL CREATE RULE SELECT CREATE SCHEMA SELECT INTO CREATE SEQUENCE SET CREATE SERVER SET CONSTRAINTS CREATE TABLE SET ROLE CREATE TABLE AS SET SESSION AUTHORIZATION CREATE TABLESPACE SET TRANSACTION CREATE TEXT SEARCH CONFIGURATION SHOW CREATE TEXT SEARCH DICTIONARY START TRANSACTION CREATE TEXT SEARCH PARSER TABLE CREATE TEXT SEARCH TEMPLATE TRUNCATE CREATE TRANSFORM UNLISTEN CREATE TRIGGER UPDATE CREATE TYPE VACUUM CREATE USER VALUES CREATE USER MAPPING WITH CREATE VIEW postgres=# ? General copyright show PostgreSQL usage and distribution terms 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 q quit psql crosstabview [COLUMNS] execute query and display results in crosstab 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 [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) 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 db[+] [PATTERN] list tablespaces dc[S+] [PATTERN] list conversions dC[+] [PATTERN] list casts dd[S] [PATTERN] show object descriptions not displayed elsewhere ddp [PATTERN] list default privileges dD[S+] [PATTERN] list domains 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 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,and sequence access privileges drds [PATRN1 [PATRN2]] list per-database role settings 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 dE[S+] [PATTERN] list foreign tables 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 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 (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null| numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pa ger| unicode_border_linestyle|unicode_column_linestyle|unicode_header_line style}) 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") encoding [ENCODING] show or set client encoding password [USERNAME] securely change the password for a user conninfo display information about current connection 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 postgres=# ------------------------------------------------------------ 到此postgresql安装完成，可以开始正常服务，大家如果想创建自己的数据库，用户和密码可以使用： 验证使用： 添加新用户和创建数据库 postgres=# create user luxuefeng with password 'luxuefeng'; CREATE ROLE postgres=# create database testdb with encoding='utf8' owner=luxuefeng; CREATE DATABASE postgres=# postgres=# create table test_user(id int,name varchar(20)); CREATE TABLE postgres=# insert into table test_user values(123,'lxf'); ERROR: syntax error at or near "table" LINE 1: insert into table test_user values(123,'lxf'); ^ postgres=# insert into test_user values(123,'lxf'); INSERT 0 1 postgres=# select * from test_user; id | name -----+------ 123 | lxf (1 row) postgres=#
远程连接配置： 远程连接配置： /usr/postgresql/psql/data/pg_hba.conf # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 0.0.0.0/0 trust host all all 192.168.1.0/32 trust /usr/postgresql/psql/data/postgresql.conf 将该文件中的listen_addresses项值设定为“*” （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |