Postgres

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%']);