Pgql 数据库登录、操作数据库 、设置密码、导入数据、
2023-09-27 14:21:38 时间
设置默认密码
设置默认密码 [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:\~]$
相关文章
- 【数据库开发】MySQL修改root密码
- C#.NET 大型企业信息化系统集成快速开发平台 4.2 版本 - 防止暴力破解密码、提高大型信息系统安全
- 利用数据库查表瓶颈,对抗密码破解
- 用phpMyAdmin修改mysql数据库密码
- Linux登录ssh携带密码
- 公司网络安全弱密码加固简单处理
- 【ERROR: ORA-28002: the password will expire within 7 days】Oracle数据库长期未使用,密码即将过期,解决办法!(已更新方法)
- NextCloud配置邮箱,用于重置密码、通知使用
- 【Linux】Linux——修改root用户密码
- JS的常用正则表达式 验证密码(转载自用)
- Navicat如何为单独数据库分配单独账户和密码
- FastAPI 学习之路(二十九)使用(哈希)密码和 JWT Bearer 令牌的 OAuth2
- 在OpenErp的配置文件中为数据库密码加密
- 2018-8-10-win10-UWP-修改密码框文字水平
- 破解Mysql数据库的管理员密码(不时之需)
- 【Git系列】linux每次git clone都要输入密码