Svolence

pgsql basic usage

Posted on  |    |   In   |     |   Visitors

postgresql 使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
cd /var/opt/gitlab/postgresql/data
vim pg_hba.conf
host all all 0.0.0.0/0 md5  #0.0.0.0为地址段,0为多少二进制位
host    all    all  10.96.23.0/24  trust    添加这行:10.96.23.0/24这个段的ip地址不需要密码可以连接
host    all    all  10.96.23.0/24  md5      添加这行:10.96.23.0/24这个段的ip链接需要密码
vim postgresql.conf
listen_addresses = '10.96.23.104'      修改监听地址为ip
gitlab-ctl restart postgresql
cd /opt/gitlab/embedded/
bin/psql -U gitlab -d gitlabhq_production -h 10.96.23.102             不用输入密码就可进来
psql (9.2.18)
Type "help" for help.

命令行登陆数据库

1
psql -U gitlab -d gitlabhq_production -h 10.96.23.104

常用命令

1.列出所有的数据库

1
2
mysql:show databases
psql:\l或list

2.切换数据库

1
2
mysql: use dbname
psql: \c dbname

3.列出当前数据库下的数据表

1
2
mysql: show tables
psql: \d

4.列出指定表的所有字段

1
2
mysql: show columns from table name
psql: \d tablename

5.查看指定表的基本情况

1
2
mysql: describe tablename
psql: \d+ tablename

6.删除数据库

1
2
3
4
# 删除数据库,首先需要切到postgres用户
sudo-u postgres psql postgres
dropdb <database-name>

7.删除用户

1
dropuser <user-name>

8.退出登录

1
2
mysql: quit 或者\q
psql:\q
1
2
3
4
5
6
7
sudo su postgres #切换至postgres
psql postgres #登入默认数据库
以上可以合并成一条命令：sudo -u postgres psql postgres
\password postgres #给postgres用户设置密码

新建gitlab用户和gitlabhq_production数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
postgres@oceanwen:/home/oceanwen$ psql postgres
psql (9.5.4)
Type "help" for help.
postgres=#
postgres=#
postgres=#
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
postgres@oceanwen:/home/oceanwen$ createuser --help
createuser creates a new PostgreSQL role.
Usage:
  createuser [OPTION]... [ROLENAME]
Options:
  -c, --connection-limit=N  connection limit for role (default: no limit)
  -d, --createdb            role can create new databases
  -D, --no-createdb         role cannot create databases (default)
  -e, --echo                show the commands being sent to the server
  -E, --encrypted           encrypt stored password
  -g, --role=ROLE           new role will be a member of this role
  -i, --inherit             role inherits privileges of roles it is a
                            member of (default)
  -I, --no-inherit          role does not inherit privileges
  -l, --login               role can login (default)
  -L, --no-login            role cannot login
  -N, --unencrypted         do not encrypt stored password
  -P, --pwprompt            assign a password to new role
  -r, --createrole          role can create new roles
  -R, --no-createrole       role cannot create roles (default)
  -s, --superuser           role will be superuser
  -S, --no-superuser        role will not be superuser (default)
  -V, --version             output version information, then exit
  --interactive             prompt for missing role name and attributes rather
                            than using defaults
  --replication             role can initiate replication
  --no-replication          role cannot initiate replication
  -?, --help                show this help, then exit
Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
Report bugs to <pgsql-bugs@postgresql.org>.
postgres@oceanwen:/home/oceanwen$ createuser -drSP gitlab
Enter password for new role:
Enter it again:
postgres@oceanwen:/home/oceanwen$ createdb --help
createdb creates a PostgreSQL database.
Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit
Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database
By default, a database with the same name as the current user is created.
Report bugs to <pgsql-bugs@postgresql.org>.
postgres@oceanwen:/home/oceanwen$ createdb -O gitlab gitlabhq_production
postgres@oceanwen:/home/oceanwen$
postgres@oceanwen:/home/oceanwen$ dropuser gitlab
dropuser: removal of role "gitlab" failed: ERROR:  role "gitlab" cannot be dropped because some objects depend on it
DETAIL:  owner of database gitlabhq_production
postgres@oceanwen:/home/oceanwen$ dropdb gitlabhq_production
dropdb: database removal failed: ERROR:  database "gitlabhq_production" is being accessed by other users
DETAIL:  There are 4 other sessions using the database.
postgres@oceanwen:/home/oceanwen$ dropdb gitlabhq_production
dropdb: database removal failed: ERROR:  database "gitlabhq_production" is being accessed by other users
DETAIL:  There are 3 other sessions using the database.
postgres@oceanwen:/home/oceanwen$ dropuser gitlab
dropuser: removal of role "gitlab" failed: ERROR:  role "gitlab" cannot be dropped because some objects depend on it
DETAIL:  owner of database gitlabhq_production
postgres@oceanwen:/home/oceanwen$ dropuser gitlab
dropuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
postgres@oceanwen:/home/oceanwen$ dropuser gitlab
dropuser: removal of role "gitlab" failed: ERROR:  role "gitlab" cannot be dropped because some objects depend on it
DETAIL:  owner of database gitlabhq_production
postgres@oceanwen:/home/oceanwen$ dropdb gitlabhq_production
dropdb: database removal failed: ERROR:  database "gitlabhq_production" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres@oceanwen:/home/oceanwen$ dropdb gitlabhq_production
dropdb: database removal failed: ERROR:  database "gitlabhq_production" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres@oceanwen:/home/oceanwen$ dropdb gitlabhq_production
postgres@oceanwen:/home/oceanwen$ dropuser gitlab
postgres@oceanwen:/home/oceanwen$

PS:(这里是因为我在别的远程主机里面有登陆,退出,sudo service postgresql restart就好了)

1
2
3
4
5
6
7
8
9
10
11
12
================================================================================
Error executing action `run` on resource 'bash[migrate gitlab-rails database]'
================================================================================
Mixlib::ShellOut::ShellCommandFailed
------------------------------------
Expected process to exit with [0], but received '1'
---- Begin output of "bash"  "/tmp/chef-script20160929-2371-1rdy2fm" ----
STDOUT: rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create extension "pg_trgm"
HINT:  Must be superuser to create this extension.
: CREATE EXTENSION IF NOT EXISTS "pg_trgm"

PS:(createuser 的时候指定 -s,赋予超级权限即可)