1

PostgreSQL 常用命令

 2 years ago
source link: https://simpleowen.github.io/post/postgresql_ops/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

查看当前主机 PostgreSQL 安装路径及版本

  • which psql
  • psql –version

修改 postgres 系统用户密码

  • sudo passwd -d postgres
  • sudo -u postgres passwd

查看当前连接信息

  • \conninfo

列出所有用户(角色)

列出所有数据库

  • \l or \list

切换数据库

  • \c database or \connect database

重命名数据库

  • 切换到不需要重命名的数据库
  • alter database old_dbname rename to new_dbname;

删除数据库

  • drop database dbname;
  • CREATE USER username WITH PASSWORD ‘password’ CREATEDB;
  • DROP USER username
  • \password username

新建数据库

  • CREATE DATABASE dbname OWNER username;

修改数据库所有者

  • alter database dbname owner to username;
  • GRANT ALL PRIVILEGES ON DATABASE “dbname” to username;

如何连接远程数据库

  • su - postgres
  • cd /etc/postgresql/10/main

修改两个配置文件

  • postgresql.conf

    • 监听地址改为任意地址: listen_addresses = '*'

  • pg_hba.conf

    • 接受任意地址的密码验证: 增加一行 -> host all all 0.0.0.0/0 md5

  • 重启服务

    • su - superuser
    • sudo service postgresql restart

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK