初始化postgresql
备份与恢复
mac安装postgres
基本命令
- 安装:
brew install postgresql -v - 初始化:
initdb /usr/local/var/postgres -E utf8 - 启动:
pg_ctl -D /usr/local/var/postgres -l logfile start - 进入数据库:
psql -U db_user -d db_name -h 127.0.0.1 -p 5432 -E - 创建数据库用户:
sudo -u postgres createuser --superuser db_user - 创建数据库:
sudo -u postgres createdb -O db_user db_name - 所有数据库:
\l - 退出数据库:
\q - 连接其他数据库:
\c [database_name] - 当前数据库所有表格:
\d - 当前数据库所有table:
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename; - 查看某个数据库:
\d dbname - 某一张表格的结构:
\d [table_name] - 列出所有用户:
\du - 当前数据库和连接的信息:
\conninfo - 修改数据库表名称:
alter table table_name rename to new_table_name - 添加字段:
ALTER TABLE table_name ADD column column_name VARCHAR(100); - 设置密码:
\password - 备份数据:
pg_dump -d db_name -t table_name -U db_user > file.sql - 导入数据:
psql -d db_name -f file.sql -U db_user -W(密码) -c(先清除数据库对象) - 以数据文件夹启动:
postgres -D /Users/free/pg_data > /opt/postgresql/log/pg_server.log 2>&1 & - 强制删除表(忽略关联):
DROP TABLE if exists table_name cascade; - 导出指定数据表：
pg_dump -t table_name db_name -a > db.sql - 导入数据：
psql -d db_name -f db.sql - 查看数据库大小:
SELECT pg_size_pretty(pg_database_size('blockscout')); - service使用:
service postgresql status - 表大小：
select pg_size_pretty(pg_total_relation_size('logs')) as size; - 表索引大小：
select pg_size_pretty(pg_relation_size('logs')) as size; - 所有表总大小：
select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables; - 查看最大连接数:
SHOW max_connections; - 设置最大连接数(设置完重启):
ALTER SYSTEM SET max_connections TO '500';
基本数据操作
-
更新数据:
UPDATE blocks SET consensus=false WHERE number=7387001; -
删除数据库:
SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'database_name'
;
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
dropdb -U postgres db_name
- 查询所以表结构：
\d+ public.*
SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'your_table'
;
导入数据时，新旧表不一样(null value in column “contract_code_md5” of relation “smart_contracts” violates not-null constraint)
ALTER TABLE my_table ALTER COLUMN xtra_col SET DEFAULT 'b';
COPY my_table (col1, col2, col3) FROM '/workdir/some_file.txt' WITH (FORMAT CSV, DELIMITER '|', NULL 'NULL', HEADER true);
ALTER TABLE my_table ALTER COLUMN xtra_col DROP DEFAULT;
