Pgql 数据库登录、操作数据库 、设置密码、导入数据、
设置默认密码
设置默认密码 [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=#
linux下 postgres实现导出和导入
用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:\~]$
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
.....................................................................
------- 桃之夭夭，灼灼其华。之子于归，宜其室家。 ---------------
------- 桃之夭夭，有蕡其实。之子于归，宜其家室。 ---------------
------- 桃之夭夭，其叶蓁蓁。之子于归，宜其家人。 ---------------
=====================================================================
* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。 * @author Alan -liu * @Email no008@foxmail.com
转载请标注出处！ ✧*꧁一品堂.技术学习笔记꧂*✧. ---> https://www.cnblogs.com/ios9/
