Command
shell:
sudo -u postgres createuser --superuser dbuser
sudo -u postgres createdb -O dbuser exampledb
psql : postgres console
psql -U postgres -d postgres -h 127.0.0.1 -p 5432
psql -U postgres -f file.sql
eg:
psql -d idmdb -U idm -f chang.sql
psql -U idm -d idmdb
postgres console:
\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。
\password user: 修改密码
\q:退出
CREATE USER dbuser WITH PASSWORD 'password';
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
数据库导出:
pg_dump -U postgres -tinternal_user_configuration idmdb > internal_user_configuration.sql
[root@prop-idm propel]# pg_dump -U postgres idm_prod_20180405 > db20200404.sql
/指定 --table
pg_dump -U postgres -d idmdb \
--table "abstract_group" \
--table "abstract_user" \
"C:\Program Files\pgAdmin 4\v5\runtime\pg_dump.exe" --file=C:\Users\xtian5\idmdb_204_104_116_128-2021_11_17_15_52_43-dump_n2.sql --dbname=idmdb --username=postgres --host=204.104.116.128 --port=5432
数据库表导出:
pg_dump -U postgres idm_prod_20180405 -t datacenter > dum_datacenter.sql
pg_dump -U postgres idm_prod_20180405 -s -t permission_role > per_role.sql(-s only export schema)
数据库导入:
psql -U postgres -d idmdb -f internal_user_configuration.sql
[root@prop-idm propel]# psql -d idm_tina -f db20200404.sql postgres
[root@prop-idm propel]#psql -h 127.0.0.1 -U postgres -d idmdb_20211029 < /var/lib/postgresql/data/idmdb_20211029.bak
C:\Program Files\PostgreSQL\9.4\bin>psql -h 127.0.0.1 -U postgres -d idmdb-n2 < C:\Users\xtian5\idmdb_204_104_116_128-2021_11_17_17_06_24-dump_n2.sql
查询表的schema信息:
idm_prod_20180405=# select * from information_schema.tables;
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_inse
rtable_into | is_typed | commit_action
-------------------+--------------------+--------------------------------------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------
------------+----------+---------------
idm_prod_20180405 | pg_catalog | pg_statistic | BASE TABLE | | | | | | YES
| NO |
idm_prod_20180405 | pg_catalog | pg_type | BASE TABLE | | | | | | YES
| NO |
生成UUID
idm_db2=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
NOTICE: extension "uuid-ossp" already exists, skipping
CREATE EXTENSION
idm_db2=# SELECT uuid_generate_v1();
uuid_generate_v1
--------------------------------------
524c1ea0-9369-11ea-a2a9-080027cda55f
(1 row)
error:
Message : ERROR: function random_uuid() does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
查询每个表的记录条数
idm_prod_20180405=# select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc ;
table_name | rowcounts
----------------------------------+------------
token_store | 1.3e+06
user_statistics | 1.07541e+06
permission_role | 13639
metadata | 9069
show table sizes
idm_prod_20180405=# SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 115;
relation | total_size
---------------------------------------------------+------------
public.token_store_bak | 1074 MB
public.user_statistics | 340 MB
public.general_log | 281 MB
public.token_store | 7872 kB
Show size of all databases
idm_prod_20180405=# SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;
database_name | size_in_mb
-------------------+------------
idm_prod_20180405 | 1742
idm_temp | 1306
oo | 219
catalog | 192
Error
1. GUI client connection
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?
Solution:
1. 修改pg_hba.conf
[root@c9t21622 bin]# vim /var/lib/pgsql/9.4/data/pg_hba.conf
添加:
host all all 15.107.13.42/23 trust
或修改
#host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
2. 修改postgresql.conf (localhost -> *)
[root@c9t21622 bin]# vim /var/lib/pgsql/9.4/data/postgresql.conf
57 # - Connection Settings -
58 listen_addresses = '*'
Refer to http://www.railszilla.com/postgresql-tcpip-connections-port-5432/coffee-break
Sample
sql
\c Xdb
DO $$
BEGIN
BEGIN
alter table T add column A varchar(100);
EXCEPTION
WHEN duplicate_column THEN RAISE NOTICE 'column A already exists in T.';
END;
BEGIN
END;
END;
$$
数据库年份 + 2
select auditing_date, auditing_date + MAKE_INTERVAL(YEARS := 2023 - EXTRACT(YEAR FROM auditing_date)::INTEGER) from table1 where auditing_date > current_timestamp; select auditing_date, auditing_date + MAKE_INTERVAL(YEARS := 2) from table1 where auditing_date >= to_date('2021-01-01 00:00:00','yyyy-MM-dd')
备份表 table1
CREATE TABLE table1_tx As table table1; create table a_bk as select * from a
#systemctl status
[root@propel security]# systemctl status postgresql-9.4 -l
● postgresql-9.4.service - PostgreSQL 9.4 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2018-08-15 10:05:55 UTC; 21h ago
Process: 6067 ExecStop=/usr/pgsql-9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
Process: 6096 ExecStart=/usr/pgsql-9.4/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
Process: 6090 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 6100 (postgres)
[root@propel data] systemctl restart postgresql-9.4
SQL操作
修改表名
ALTER TABLE IF EXISTS general_log RENAME TO general_log_bk;
用已有表名schema创建新表
CREATE TABLE general_log (
like general_log_bk
including all
);
修改表owner
ALTER TABLE general_log OWNER TO newMember;
Windows change password:
C:\Program Files\PostgreSQL\9.4\bin
cmd
psql -U postgres
ALTER USER Postgres WITH PASSWORD '';
sql 生成sql
select 'update act_ru_task set assignee_ = ''' || assignee_ || ''' where id_ = '''|| id_ || ''';' FROM act_ru_task
select uuid, display_name,email from idm_tina.public.nmc_user where lower(email) not similar to '%(tian|wang)%' -- posix正则 select uuid, display_name,email from idm_tina.public.nmc_user where lower(email) !~* '(tian|wang)' select uuid, display_name,email from idm_tina.public.nmc_user where not lower(email) LIKE ANY (ARRAY['%tian%','%wang%']);