设置默认密码
设置默认密码
[root@VM_0_2_centos start-scripts]# su - postgres
Last login: Fri Nov 12 16:49:36 CST 2021 on pts/3
-bash-4.2$ psql -U postgres
psql (12.5)
Type "help" for help.
postgres=# ALTER USER postgres with encrypted password 'postgres';
ALTER ROLE
postgres=#
导入数据文件
导出数据：
[wiew@szyhdb1c ~] cd /pgsoft/pg12.5/bin
[view@szyhdblc bin] ./pg_dump -h IP -p 5432 -U 用户名称 -d 数据库名称 > /home/view/szsfs_db_2021/test_pd_dump_20211220/szsfs20211220.bak
Password:
[view@szyhdblc bin]
[view@szyhdb1c ~]# cd /usr/local/pgsql/bin/
[view@szyhdb1c bin]#./psql -s 数据库名称 -f /home/veiw/szsfs20220328_SqlUpdate.sql
==========================注：如果上述脚本执行操作出现了以下错误内容：请查看该内容进行操作==================================================================================
==| [root@localhost bin]# clear ======================
==| [root@localhost bin]# cd "/usr/local/pgsql/bin" ======================
==| [root@localhost bin]# ./psql -s 数据库名称 -f /home/postgres/szsfs20220328_SqlUpdate.sql ======================
==| psql: error: FATAL: role "root" does not exist ======================
==| [root@localhost bin]# su - postgres ======================
==| Last login: Mon Mar 28 10:57:33 CST 2022 on pts/3 ======================
==| -bash-4.2$ psql -U postgres ======================
==| psql (12.5) ======================
==| Type "help" for help. ======================
==| ======================
==| postgres=# create user root with password 'passwordSzsfsroot'; ======================
==| CREATE ROLE ======================
==| postgres=# grant all privileges on database szsfs20220220 to root; ======================
==| GRANT ======================
==| postgres=# \q ======================
==| could not save history to file "/home/postgres/.psql_history": No such file or directory ======================
==| -bash-4.2$ ======================
==| -bash-4.2$ exit ======================
==| [root@localhost bin]# ./psql -s 数据库名称 -f /home/postgres/szsfs20220328_SqlUpdate.sql
==| ***(Single step mode: verify command)*******************************************
==| alter table t_wx_reconciliation add column iSCHECKdate date ;
==| ***(press return to proceed or enter x and return to cancel)********************
==|
==| psql:/home/postgres/szsfs20220328_SqlUpdate.sql:3: ERROR: must be owner of table t_wx_reconciliation
==| ***(Single step mode: verify command)*******************************************
==| comment on COLUMN t_wx_reconciliation.ischeckdate is '缴费通知书对账时间';
==| ***(press return to proceed or enter x and return to cancel)********************
==|
==| psql:/home/postgres/szsfs20220328_SqlUpdate.sql:4: ERROR: must be owner of relation t_wx_reconciliation
==| ***(Single step mode: verify command)*******************************************
==| alter table t_paynote add column iSCHECKdate date ;
==| ***(press return to proceed or enter x and return to cancel)********************
==|
==| psql:/home/postgres/szsfs20220328_SqlUpdate.sql:5: ERROR: must be owner of table t_paynote
==| ***(Single step mode: verify command)*******************************************
==| comment on COLUMN t_paynote.ischeckdate is '缴费通知书对账时间';
==| ***(press return to proceed or enter x and return to cancel)********************
==|
==| psql:/home/postgres/szsfs20220328_SqlUpdate.sql:6: ERROR: must be owner of relation t_paynote
==| ***(Single step mode: verify command)*******************************************
==| /**
==|
==| # 注意：如果在生产环境执行上述脚本内容出现以下内容，则表示该脚本已经执行过了。无效关注
==|
==| szsfs20220220=# alter table t_wx_reconciliation add column iSCHECKdate date ;
==| ¹¦对账时间';ERROR: column "ischeckdate" of relation "t_wx_reconciliation" already exists
==| szsfs20220220=# comment on COLUMN t_wx_reconciliation.ischeckdate is '缴费通知书对账时间';
==| COMMENT
==|
============================================================================================================================================================================
验证脚本发布执行情况：
[view@szyhdb1c ~]# clear
[view@szyhdb1c ~]# cd /usr/local/pgsql/bin/
[view@szyhdb1c bin]# ./psql -h IP -p 5432 -U 用户名称 -d 数据库名称
Password for user szsfs: #注：密码请与郑猛联系
psql (12.5)
Type "help" for help.
szsfs20220220=#
用postgres 的pg_dump可以实现从从postgres数据库中导出数据。
[1]只导出所有对象的数据库结构
C:\>pg_dump -f DDDDDD.sql -i -C -E UTF8 -n public -s -U portal -h localhost -W portal
-i 是为了兼容数据库版本
-C 包括创建数据库的语句
-E 设定导出数据的编码
-n 是Scheme的名称
-U 是用户名称
-h 是数据库服务器的名称
-W 是用强制密码验证
-s 只导出数据库结构
最后一个参数，当然就是数据库名称了
[2]导出所有对象的数据库结构和数据
C:\>pg_dump -f DDDDDD.sql -i -C -E UTF8 -n public -U portal -h localhost -W portal
没有-s参数
[3]只导出所有的表数据
C:\>pg_dump -f DDDDDD.sql -i -a -C -E UTF8 -n public -U portal -h localhost -W portal
-a 只导出数据
数据导入
[1]c:\psql -f DDDDDD.sql -h 192.168.1.233 -U myuser -W myportal
执行就可以实现导入了。
如果数据库myportal 不存在，要先创建数据库
createdb -U postgres -h 192.168.1.233 myportal
然后再执行上面的导入语句就可以了。
[2]psql -hlocalhost -U myuser -d myportal < DDDDDD.sql
执行语句导入数据就可以了。
登录Pgsql 数据库
To escape to local shell, press 'Ctrl+Alt+]'.
Last login: Mon Mar 28 09:30:23 2022 from 192.168.255.4
[root@localhost home]# cd /usr/local/pgsql/bin/
[root@localhost bin]# ./psql -h 127.0.0.1 -p 5432 -U szsfs20220220 -d szsfs20220220
psql (12.5)
Type "help" for help.
szsfs20220220=#
[root@localhost bin]# clear
[root@localhost bin]# su - postgres
Last login: Mon Mar 28 10:02:24 CST 2022 on pts/1
-bash-4.2$
-bash-4.2$ psql -U postgres
psql (12.5)
Type "help" for help.
postgres=# \c szsfs20220220
You are now connected to database "szsfs20220220" as user "postgres".
szsfs20220220=# \d t_paynote
Table "public.t_paynote"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------------+-----------+----------+---------
pn_no | character varying(35) | | not null |
unit_no | character varying(30) | | not null |
unit_name | character varying(200) | | |
payer_name | character varying(300) | | |
payer_accountno | character varying(50) | | |
receivable_amt | numeric(16,2) | | |
pn_received_amt | numeric(16,2) | | |
overdue_amt | numeric(16,2) | | |
create_tm | timestamp(6) without time zone | | |
paid_tm | timestamp(6) without time zone | | |
rt_no | character varying(6) | | |
receipt_no | character varying(20) | | |
account_no | character varying(50) | | |
bank_no | character varying(30) | | |
bank_name | character varying(200) | | |
st_type | numeric(10,0) | | |
business_type | numeric(10,0) | | |
status | numeric(10,0) | | |
bind_status | numeric(10,0) | | |
remark | character varying(200) | | |
proctime | timestamp(6) without time zone | | |
procuserid | numeric(10,0) | | |
detailiteminfo | character varying(500) | | |
fullcheckcode | character varying(5) | | |
numcheckcode | character varying(5) | | |
additionalamount | numeric(16,2) | | |
paytype | numeric(10,0) | | |
batchno | numeric(10,0) | | |
acc_file_name | character varying(200) | | |
wt_file_name | character varying(80) | | |
acc_file_up_day | timestamp(6) without time zone | | |
wt_file_down_day | timestamp(6) without time zone | | |
payee_no | character varying(30) | | |
payee_name | character varying(100) | | |
id | numeric(20,0) | | not null |
refund_bank_name | character varying(200) | | |
refund_bank_no | character varying(30) | | |
修改数据名称、查看数据库信息
[root@192 ~]# su - postgres
Last login: Mon Mar 28 14:27:10 UTC 2022
-bash-4.2$ psql -U szsfs20220220
psql (12.5)
Type "help" for help.
^
szsfs20220220=# update pg_database set datname ='szsfs20220329' where datname='szsfs20220220';
UPDATE 1
szsfs20220220=# \c postgres
You are now connected to database "postgres" as user "szsfs20220220".
postgres=# \c szsfs20220220
FATAL: database "szsfs20220220" does not exist
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+---------------+----------+-------------+-------------+---------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
szsfs20220329 | szsfs20220220 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/szsfs20220220 +
| | | | | szsfs20220220=CTc/szsfs20220220+
| | | | | root=CTc/szsfs20220220
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres-#
Connection closed by foreign host.
Disconnected from remote host(confluence) at 05:53:41.
Type `help' to learn how to use Xshell prompt.
[C:\~]$