一：建庫建使用者初始化槽位指令碼
-- 172.30.206.63
drop database if exists gzdg;
drop user if exists u_gzdg;
create user u_gzdg with password 'P_gzdg2w!Q';
create database gzdg owner u_gzdg encoding 'UTF8';
drop database if exists config;
drop user if exists u_config;
create user u_config with password 'P_config2w!Q';
create database config owner u_config encoding 'UTF8';
drop database if exists seq;
drop user if exists u_seq;
create user u_seq with password 'P_seq2w!Q';
create database seq owner u_seq encoding 'UTF8';
drop database if exists persontax;
drop user if exists u_persontax;
create user u_persontax with password 'P_persontax2w!Q';
create database persontax owner u_persontax encoding 'UTF8';
drop database if exists ac;
drop user if exists u_ac;
create user u_ac with password 'P_ac2w!Q';
create database ac owner u_ac encoding 'UTF8';
drop database if exists apicore;
drop user if exists u_apicore;
create user u_apicore with password 'P_apicore2w!Q';
create database apicore owner u_apicore encoding 'UTF8';
drop database if exists invoice;
drop user if exists u_invoice;
create user u_invoice with password 'P_invoice2w!Q';
create database invoice owner u_invoice encoding 'UTF8';
華為雲插槽
select * from pg_create_logical_replication_slot('fgk_data_slot', 'decoderbufs');
-- 建立槽
-- 建立
SELECT 'init' FROM pg_create_logical_replication_slot('data_slot', 'wal2json');
-- 查詢驗證
select * from pg_replication_slots;
-- 刪除
select pg_drop_replication_slot('data_slot');
-- 授權
ALTER ROLE u_ac REPLICATION;
-- 建立外掛
CREATE EXTENSION IF NOT EXISTS orafce SCHEMA public;
create cast (character varying as bigint) with inout as implicit;
create cast (bigint as character varying) with inout as implicit;
-- P_dev@123
-- dump命令
--format=custom 格式化輸出檔案自定義格式（實際相當於生成位元組檔案）
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_ac --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_ac_dump.dump
--role=u_gzdg 指定生成的結構歸屬的使用者
--clean 生成清理指令碼 與 --if-exists 共同使用
--no-owner 忽略生成檔案中的owner資訊
--no-tablespaces 忽略生成檔案中的tablespaces
pg_restore.exe --host=172.30.206.63 --port=5432 --username=postgres --dbname=gzdg --schema=public --role=u_gzdg --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_ac_dump.dump
使用
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_ac --schema=public --no-owner --no-tablespaces --file=C:\Users\szyh\Desktop\back\dev_ac_dump.sql
psql --host=172.30.206.63 --port=5432 --username=u_gzdg --dbname=gzdg --file=C:\Users\szyh\Desktop\back\dev_ac_dump.sql
建立管理員賬戶：
CREATE ROLE admin WITH LOGIN PASSWORD 'password' CREATEDB;
CREATE ROLE yhadmin superuser PASSWORD 'MhxzKhl@12345' login; 驗證可行
grant all privileges on database kangkang to yhadmin; 單庫授權
二：資料庫匯入匯出指令碼
psql --host=172.30.198.12 --port=5432 --username=postgres --dbname=postgres --file="C:\Users\szyh\Desktop\back\a\pg create user database.sql"
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_ac --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_ac_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_ac --dbname=ac --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_ac_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_seq_database --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_seq_database_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_seq --dbname=seq --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_seq_database_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_persontax --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_persontax_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_persontax --dbname=persontax --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_persontax_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_apicore --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_apicore_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_apicore --dbname=apicore --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_apicore_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_invoice --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_invoice_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_invoice --dbname=invoice --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_invoice_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_config --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_config_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_config --dbname=config --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_config_dump.dump
pg_dump.exe --host=172.30.198.33 --port=5432 --username=postgres --dbname=dev_gzdg --schema=public --format=custom --file=C:\Users\szyh\Desktop\back\dev_gzdg_dump.dump
pg_restore.exe --host=172.30.198.12 --port=5432 --username=u_gzdg --dbname=gzdg --schema=public --clean --if-exists --no-owner --no-tablespaces C:\Users\szyh\Desktop\back\dev_gzdg_dump.dump
psql -h 192.168.2.111 -p 5432 -U user1 -W -d agg_auh -f "agg_auh.sql"
psql -h 192.168.2.111 -p 5432 -U user1 -W -d agg_auh -f "agg_auh.sql"
psql -h 192.168.2.111 -p 5432 -U user1 -W -d agg_auh -f "agg_auh.sql"
psql -h 192.168.2.111 -p 5432 -U user1 -W -d agg_auh -f "agg_auh.sql"