pgsql 常用登陆命令

##sample 1 pgsql 忘记密码怎么登陆
https://www.geeksforgeeks.org/postgresql-reset-password-for-postgres/

n this article, we will look into the step-by-step process of resetting the Postgres user password in case the user forgets it.

PostgreSQL uses the pg_hba.conf configuration file stored in the database data directory (e.g., C:\Program Files\PostgreSQL\12\data on Windows) and is used to handle user authentication. The hba in pg_hba.conf means host-based authentication.

As resetting the password requires modification in the pg_hba.conf file, you will need to login to the Postgres role without any password.

Follow the below steps to reset a password for the postgres user:

Step 1: Create a backup of the pg_hba.conf file by copying it to a different location or just rename it to pg_hba.conf.bk
Step 2: Now change the pg_hba.conf file by making all local connections from md5 to trust. This will help you to log in to the PostgreSQL database server without using a password.
# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
Step 3: Now restart the PostgreSQL server. On a Windows machine, you can restart the PostgreSQL from Services.
Or use the below command from the window terminal:

pg_ctl -D "C:\Program Files\PostgreSQL\12\data" restart
The “C:\Program Files\PostgreSQL\12\data” is the data directory.

Step 4: Finally connect to the PostgreSQL database server using any tool such as psql or pgAdmin(In pgAdmin, press ok while it prompts you to enter the password without entering anything in the field.):
psql -U postgres
At this stage, you will not be asked for any authentication.

Step 5: Use the below command to set a new password for the postgres user.
ALTER USER postgres WITH PASSWORD 'new_password';
This will change the user’s password as shown below:

 

Step 6:Now restart the PostgreSQL database server. At this stage, you can connect to the PostgreSQL database server with the new password.
Follow the above steps to successfully reset the Postgres password and do not forget to restore the pg_hba.conf file after the reset to successfully store the credentials for future verification.

 

##sample2 查询表结构

select *
from users
where false;

###sample 3 密码登陆pg sql 的方法
https://blog.csdn.net/weixin_43431593/article/details/105246647

密码登陆pg sql 的方法
PGPASSWORD=HfQchrS5 psql -U postgres -h 127.0.0.1 --port 58083 -d i2soft
或者
PGPASSWORD=HfQchrS5 psql -U postgres -h 127.0.0.1 --port 58083 -d i2soft -q -c "update users set password='\$2y\$10\$sz.Nyy677HMNNM4TU9j1muwMRdeHDPoIFC51hRv1rqxOGjb0NC04m',active=1 where username='sysadmin';"

######sample 4 修改用户的密码的方法
新版本；
ALTER USER admin PASSWORD 'dd1234';

旧版本
PGPASSWORD=HfQchrS5 psql -U postgres -h 127.0.0.1 --port 58083 -d i2soft -q -c "update users set password='\$2y\$10\$sz.Nyy677HMNNM4TU9j1muwMRdeHDPoIFC51hRv1rqxOGjb0NC04m',active=1 where username='sysadmin';"

 

 

###sample 5  登陆报错  libreadline.so.8

##issue 1
[root@jcjg-2487 ~]# PGPASSWORD=HfQchrS5 psql -U postgres -h 127.0.0.1 --port 58083 -d i2soft
psql: error while loading shared libraries: libreadline.so.8: cannot open shared object file: No such file or directory

[root@jcjg-2487 ~]# find / -name libreadline.so.8
/usr/cntlcenter/libs/lib/libreadline.so.8

　其实，对于由普通用户自己编译生成的.so库文件，
比较好的做法是将这些.so库文件的路径用export指令加入到~/.bash_profile中的LD_LIBRARY_PATH变量中,LD_LIBRARY_PATH是程序运行需要链接.so库时会去查找的一个目录，~/.bash_profile是登陆或打开shell时会读取的文件，这样，每次用户登录时，都会把这些.so库文件的路径写入LD_LIBRARY_PATH，这样就可以正常地使用这些.so库文件了。
add lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/cntlcenter/libs/lib/

 

 

 

######sample 6

问题，pg_ctl reload重启服务报错。
-bash-4.2$ cd /usr/cntlcenter/pgsql/bin
-bash-4.2$ ls
clusterdb dropdb initdb pgbench pg_controldata pg_dumpall pg_recvlogical pg_rewind pg_upgrade postgresql-12-check-db-dir psql
createdb dropuser pg_archivecleanup pg_checksums pg_ctl pg_isready pg_resetwal pg_test_fsync pg_waldump postgresql-12-setup reindexdb
createuser ecpg pg_basebackup pg_config pg_dump pg_receivewal pg_restore pg_test_timing postgres postmaster vacuumdb
-bash-4.2$ ./pg_c
pg_checksums pg_config pg_controldata pg_ctl
-bash-4.2$ ./pg_ctl reload
pg_ctl: directory "/var/lib/pgsql/11/data" is not a database cluster directory

 

解决方法：

1.ps -ef|grep pgsql
找到database 目录：

2.pg_ctl reload 加入-D 参数
/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data -l logfile start
pg_ctl restart -D /usr/cntlcenter/data/pgsql/12

 

 

####sample 7

###issue 2 修改参数 允许登陆pg

PostgreSQL安装后，貌似默认是只接受本地机器连接访问。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相应的配置。以下是我配置远程连接PostgreSQL数据库方式：

修改配置文件：（PostgreSQL安装路径下的data，也是安装时data的默认路径）data目录下的pg_hba.conf和postgresql.conf。

1、pg_hba.conf配置PostgreSQL数据库的访问权限。

找到“# IPv4 local connections:“后，回车另起一行，添加参数行如下，保存即可。
host all all 0.0.0.0/0 trust

如图所示

 

2、postgresql.conf配置PostgreSQL数据库服务器的相应的参数。

找到“listen_addresses“参数后，设置listen_addresses = '*'，保存即可。

不过我安装的版本是postgresql-10.6-1-windows-x64.exe，postgresql.conf默认的配置就是listen_addresses = '*'

 

###sample 8

注意：配置过程中，如果之前没有连接过该数据库，会自动下载驱动程序；另外，像Oracle、MS SQL这种商业数据库，需要你自行下载JDBC驱动jar包，进行加载配置。

连接完成后，最终的界面如图：

 

如果想调出SQL编辑器，可以在连接上点击右键 - 选择“SQL编辑器”

 

如果您觉得文章对您有用，请点赞收藏，谢谢～
————————————————
版权声明：本文为CSDN博主「阿福Chris」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/chrisy521/article/details/121378553