psql常用命令

2018年08月21日

常用查询语句

登录数据库

psql -U stellar -d core -h 127.0.0.1 -p 5432

其中stellar是用户，core指向数据库

sudo -u postgres psql

这个以postgres用户进入psql数据库

创建用户

sudo -u postgres createuser dbuser

或

sudo -u postgres psql
postgres=# CREATE USER dbuser WITH PASSWORD 'password';

更改密码

postgres=# \password dbuser
postgres=# \q

创建数据库

postgres=# CREATE DATABASE exampledb OWNER dbuser;
postgres=# GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
postgres=# \c exampledb;
postgres=# ALTER SCHEMA public OWNER to dbuser;
postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;

或

sudo -u postgres createdb -O dbuser exampledb

查看所有数据库

\l

执行结果

                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-----------+----------+-----------+---------+-------+-----------------------
 core      | postgres | SQL_ASCII | C       | C     | =Tc/postgres         +
           |          |           |         |       | postgres=CTc/postgres+
           |          |           |         |       | stellar=CTc/postgres
 horizon   | postgres | SQL_ASCII | C       | C     | =Tc/postgres         +
           |          |           |         |       | postgres=CTc/postgres+
           |          |           |         |       | stellar=CTc/postgres
 postgres  | postgres | SQL_ASCII | C       | C     |
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(5 rows)

切换数据库

postgres=# \c horizon

上述命令切换到horizon数据库

查看所有用户

\du

执行结果

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 stellar   |                                                            | {}

查看所有表

\d

执行结果

            List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+---------
 public | accountdata   | table | stellar
 public | accounts      | table | stellar
 public | ban           | table | stellar
 public | ledgerheaders | table | stellar
 public | offers        | table | stellar
 public | peers         | table | stellar
 public | publishqueue  | table | stellar
 public | pubsub        | table | stellar
 public | scphistory    | table | stellar
 public | scpquorums    | table | stellar
 public | signers       | table | stellar
 public | storestate    | table | stellar
 public | trustlines    | table | stellar
 public | txfeehistory  | table | stellar
 public | txhistory     | table | stellar
(15 rows)

查看表结构

postgres=# \d accounts

执行结果

core=# \d accounts
              Table "public.accounts"
    Column     |         Type          | Modifiers
---------------+-----------------------+-----------
 accountid     | character varying(56) | not null
 balance       | bigint                | not null
 seqnum        | bigint                | not null
 numsubentries | integer               | not null
 inflationdest | character varying(56) |
 homedomain    | character varying(32) | not null
 thresholds    | text                  | not null
 flags         | integer               | not null
 lastmodified  | integer               | not null
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (accountid)
    "accountbalances" btree (balance) WHERE balance >= 1000000000
Check constraints:
    "accounts_balance_check" CHECK (balance >= 0)
    "accounts_numsubentries_check" CHECK (numsubentries >= 0)

查看数据库所有表的记录总数

select relname as TABLE_NAME, reltuples as rowCounts from pg_class 
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') 
order by rowCounts desc;

查询结果

  table_name   | rowcounts
---------------+-----------
 scphistory    |     23955
 peers         |       148
 scpquorums    |         1
 offers        |         0
 trustlines    |         0
 storestate    |         0
 pubsub        |         0
 txhistory     |         0
 txfeehistory  |         0
 publishqueue  |         0
 ban           |         0
 accountdata   |         0
 ledgerheaders |         0
 accounts      |         0
 signers       |         0
(15 rows)

常用控制命令

\password           设置密码。
\q                  退出。
\h                  查看SQL命令的解释，比如\h select。
\?                  查看psql命令列表。
\l                  列出所有数据库。
\c [database_name]  连接其他数据库。
\d                  列出当前数据库的所有表格。
\d [table_name]     列出某一张表格的结构。
\du                 列出所有用户。
\e                  打开文本编辑器。
\conninfo           列出当前数据库和连接的信息。

基本的 SQL 语句

# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 查询记录
SELECT * FROM user_tbl;

# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;

# 添加字段
ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更改字段类型
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 设置字段默认值（注意字符串使用单引号）
ALTER TABLE user_tbl ALTER COLUMN email SET DEFAULT 'example@example.com';

# 去除字段默认值
ALTER TABLE user_tbl ALTER email DROP DEFAULT;

# 重命名字段
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除字段
ALTER TABLE user_tbl DROP COLUMN email;

# 表重命名
ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表
DROP TABLE IF EXISTS backup_tbl;

# 删除库
\c hello2;
DROP DATABASE IF EXISTS hello;

# 修改数据库字段名称
ALTER TABLE "trust_line" RENAME COLUMN "trustline_id" TO "trust_line_id";

备份、恢复

  • pg_dump 备份
  • pg_dumpall 备份所有数据库
  • pg_restore 恢复
  • psql exampledb < exampledb.sql 导入数据

example

pg_dump --format=t -d db_name -U user_name -W -h 127.0.0.1 > dump.sql
pg_restore -d db_name -h 127.0.0.1 -U user_name < dump.sql
# 注意要加 -U 并且一定要是 db_name 的 owner