postgresql 常用命令

2019-12-09 19:37:25

yum install postgresql-server postgresql

su - postgres

initdb -D /var/lib/pgsql/data

bash-4.1$ service postgresql start

Starting postgresql service: /etc/init.d/postgresql: line 114: echo: write error: Permission denied

bash-4.1$ exit

exit

[root@cdh2 local]# service postgresql start

Starting postgresql service: [ OK ]

[root@cdh2 local]#

[root@cdh2 local]# su - postgres

-bash-4.1$

-bash-4.1$ service postgresql status

postmaster (pid 9745) is running...

-bash-4.1$

http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

http://blog.chinaunix.net/uid-26184465-id-3527130.html

http://blog.chinaunix.net/uid-22920230-id-3493064.html

cat /var/liinitdb -D /var/lib/pgsql/data^C

-bash-4.1$ service postgresql status

postmaster (pid 9745) is running...

-bash-4.1$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres)

-bash-4.1$ vim /var/lib/pgsql/data/postgresql.conf

-bash-4.1$ cat /var/lib/pgsql/data/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 CIDR-ADDRESS METHOD [OPTIONS]

# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]

# hostnossl DATABASE USER CIDR-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", a database name, or

# a comma-separated list thereof.

# 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.

# CIDR-ADDRESS specifies the set of hosts the record matches.

# 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. Alternatively, you can write

# an IP address and netmask in separate columns to specify the set of hosts.

# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", "krb5",

# "ident", "pam", "ldap" or "cert". Note that "password" sends passwords

# in clear text; "md5" is preferred since it sends 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" or

# "samerole" 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 postmaster receives

# a SIGHUP signal. If you edit the file on a running system, you have

# to SIGHUP the postmaster for the changes to take effect. You can use

# "pg_ctl reload" to do that.

# 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.

# CAUTION: Configuring the system for local "trust" authentication allows

# any local user to connect as any PostgreSQL user, including the database

# superuser. If you do not trust all your local users, use another

# authentication method.

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

#host all all 127.0.0.1/32 trust

host all all 0.0.0.0/0 trust

# IPv6 local connections:

host all all ::1/128 trust

-bash-4.1$ service postgresql restart

Stopping postgresql service: [FAILED]

Starting postgresql service: /etc/init.d/postgresql: line 114: echo: write error: Permission denied

touch: cannot touch `/var/lock/subsys/postgresql': Permission denied

/etc/init.d/postgresql: line 122: /var/run/postmaster.5432.pid: Permission denied

-bash-4.1$ exit

logout

[root@cdh2 local]# service postgresql restart

Stopping postgresql service: [ OK ]

Starting postgresql service: [ OK ]

[root@cdh2 local]# su - postgres

-bash-4.1$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres)

-bash-4.1$ psql -U postgres -W

Password for user postgres:

psql (8.4.20)

Type "help" for help.

postgres=# CREATE USER <user> WITH PASSWORD '<password>';

ERROR: syntax error at or near "<"

LINE 1: CREATE USER <user> WITH PASSWORD '<password>';

postgres=# CREATE USER postUser WITH PASSWORD 'postPwd';

CREATE ROLE

postgres=# CREATE DATABASE test OWNER postUser ENCODING 'UTF8';

CREATE DATABASE

postgres=#

postgres=# psql -U kongxx -d test

postgres-# psql -U postUser -d test

postgres-# id

postgres-# psql --version

postgres-# exit

postgres-# help

postgres-# "help"

postgres-# psql -h localhost -U postgres -d test

postgres-# ls/

postgres-# CREATE DATABASE test1 OWNER postgres ENCODING 'UTF8'

postgres-# psql -U postgres -d test1

postgres-# ^Z

[1]+ Stopped psql -U postgres -W

-bash-4.1$ psql -U postgres -d test1

psql: FATAL: database "test1" does not exist

-bash-4.1$ psql -U postUser -d test

psql: FATAL: role "postUser" does not exist

-bash-4.1$ CREATE USER postUser WITH PASSWORD 'postPwd';

-bash: CREATE: command not found

-bash-4.1$ psql -U postgres -W

Password for user postgres:

psql (8.4.20)

Type "help" for help.

postgres=# CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8';

ERROR: database "test" already exists

postgres=# CREATE DATABASE test2 WITH OWNER = postgres ENCODING = 'UTF8';

CREATE DATABASE

postgres=# /psql -h localhost -U postgres -d test2

postgres-# psql -h localhost -U postgres -d test2

postgres-# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

: postgres=CTc/postgres

: postgres=CTc/postgres

(5 rows)

postgres-# psql --version

postgres-# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

: postgres=CTc/postgres

: postgres=CTc/postgres

(5 rows)

postgres-# select * from pg_database;

ERROR: syntax error at or near "/"

LINE 1: /psql -h localhost -U postgres -d test2

postgres=# select * from pg_database;

config | datacl

-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----

-------+-------------------------------------

template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 11563 | 648 | 1663 |

| {=c/postgres,postgres=CTc/postgres}

template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 11563 | 648 | 1663 |

| {=c/postgres,postgres=CTc/postgres}

postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

test | 16384 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

test2 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

(5 rows)

postgres=# select * from pg_database;

config | datacl

-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+----

-------+-------------------------------------

template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 11563 | 648 | 1663 |

| {=c/postgres,postgres=CTc/postgres}

template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 11563 | 648 | 1663 |

| {=c/postgres,postgres=CTc/postgres}

postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

test | 16384 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

test2 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t | -1 | 11563 | 648 | 1663 |

(5 rows)

postgres=# \dt

No relations found.

postgres=# \c test

Password for user postgres:

psql (8.4.20)

You are now connected to database "test".

test=# \dt

No relations found.

test=# CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

CREATE TABLE

test=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | user_tbl | table | postgres

(1 row)

test=# SELECT * FROM user_tbl;

name | signup_date

------+-------------

(0 rows)

test=# INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

INSERT 0 1

test=# SELECT * FROM user_tbl;

name | signup_date

------+-------------

张三 | 2013-12-22

(1 row)

test=# commit;

WARNING: there is no transaction in progress

COMMIT

test=# UPDATE user_tbl set name = '李四' WHERE name = '张三';

UPDATE 1

test=# SELECT * FROM user_tbl;

name | signup_date

------+-------------

李四 | 2013-12-22

(1 row)

test=# ALTER TABLE user_tbl ADD email VARCHAR(40);

ALTER TABLE

test=# desc user_tbl

test-# ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

ERROR: syntax error at or near "desc"

LINE 1: desc user_tbl

test=# ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

ALTER TABLE

test=# ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

ALTER TABLE

test=# ALTER TABLE user_tbl RENAME TO backup_tbl;

ALTER TABLE

test=# \dt

List of relations

Schema | Name | Type | Owner

--------+------------+-------+----------

public | backup_tbl | table | postgres

(1 row)

test=# \du

List of roles

Role name | Attributes | Member of

-----------+-------------+-----------

postgres | Superuser | {}

: Create role

: Create DB

postuser | | {}

test=# \conninfo

Invalid command \conninfo. Try \? for help.

test=# \conninfo

Invalid command \conninfo. Try \? for help.

test=# \dt

List of relations

Schema | Name | Type | Owner

--------+------------+-------+----------

public | backup_tbl | table | postgres

(1 row)

test=# \d backup_tbl

Table "public.backup_tbl"

Column | Type | Modifiers

--------+-----------------------+-----------

name | character varying(20) |

signup | date | not null

email | character varying(40) |

test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;

ERROR: relation "user_tbl" does not exist

test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;

ERROR: relation "user_tbl" does not exist

test=# ALTER TABLE backup_tbl RENAME to user_tbl;

ALTER TABLE

test=# \d backup_tbl

Did not find any relation named "backup_tbl".

test=# \dt

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | user_tbl | table | postgres

(1 row)

test=# \d user_tbl

Table "public.user_tbl"

Column | Type | Modifiers

--------+-----------------------+-----------

name | character varying(20) |

signup | date | not null

email | character varying(40) |

test=# ALTER TABLE user_tbl RENAME COLUMN signup TO signup_todate;

ALTER TABLE

test=# \d user_tbl

Table "public.user_tbl"

Column | Type | Modifiers

---------------+-----------------------+-----------

name | character varying(20) |

signup_todate | date | not null

email | character varying(40) |

test=# \?

General

\copyright show PostgreSQL usage and distribution terms

\g [FILE] or ; execute query (and send results to file or |pipe)

\h [NAME] help on syntax of SQL commands, * for all commands

\q quit psql

Query Buffer

\e [FILE] edit the query buffer (or file) with external editor

\ef [FUNCNAME] edit function 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

\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[+] [PATTERN] list aggregates

\db[+] [PATTERN] list tablespaces

\dc[S] [PATTERN] list conversions

\dC [PATTERN] list casts

\dd[S] [PATTERN] show comments on objects

\dD[S] [PATTERN] list domains

\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

--More--

test=# SELECT version();

version

-----------------------------------------------------------------------------------------------------------------

PostgreSQL 8.4.20 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 32-bit

(1 row)

test=#

————————————————

原文链接：https://blog.csdn.net/hzdxw/article/details/51794451

分享好友

分享这个小栈给你的朋友们，一起进步吧。

PostgreSQL中文社区小栈

创建时间：2019-04-19 17:47:49

PostgreSQL中文社区是一个非盈利的民间组织，已经在中国成功举办过8届技术大会。目前成员都以志愿者身份加入，成立的目的在于构建PG数据库技术生态圈子（内核、用户、培训机构、厂商、服务商、软件开发商、高校形成“业务与利益双向驱动”的良性发展生态圈）；帮助企业解决人才培养和企业商用数据库成本问题。社区会在各运营平台发布PG新信息和PG相关技术文章，推动PG技术在中国的发展。

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

栈主、嘉宾

查看更多

wangliyun
栈主
digoal

嘉宾
飘絮絮絮丶

嘉宾

小栈成员

查看更多

栈栈
喵呜
osdba
一号管理员