【PostgreSQL】安装和常用命令教程

07-13 1022阅读

PostgreSQL window安装教程

window安装PostgreSQL

建表语句:

DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id serial NOT NULL,
  name varchar(100) NOT NULL,
  sex varchar(5) NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO student (id, name, sex) VALUES (1, '小明', '男');
INSERT INTO student (id, name, sex) VALUES (2, '小红', '女');
CREATE TABLE IF NOT EXISTS score
(
    id integer NOT NULL,
    student_id integer NOT NULL,
    grade integer NOT NULL,
    PRIMARY KEY (id)
);
insert into score(id,student_id,grade)values(1,1,80);
insert into score(id,student_id,grade)values(2,2,90);
PostgreSQL自增
MySQL 是用 AUTO_INCREMENT 这个属性来标识字段的自增。
PostgreSQL 使用序列来标识字段的自增长:
CREATE TABLE runoob
(
    id serial NOT NULL,
    alttext text,
    imgurl text
)

https://www.runoob.com/postgresql/postgresql-autoincrement.html

PostgreSQL表的创建

PostgreSQL表的创建

PostgreSQL linux安装教程1——centos6

linux安装PG教程

(1)安装依赖包

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

(2)下载PG安装包

http://www.postgresql.org/ftp/source/

【PostgreSQL】安装和常用命令教程

(3)解压安装

在根目录下新建pgsql文件夹,并将pgsql的压缩包移入

tar -zxvf postgresql-15.3.tar.gz

进入解压后的文件夹,编译postgresql源码

cd postgresql-15.3/
./configure --prefix=/pgsql/postgresql

make:

make
make install

进入/pgsql/postgresql目录可以看到安装后的postgresql的文件。

【PostgreSQL】安装和常用命令教程

(5)创建用户组postgres并创建用户postgres

groupadd postgres
useradd -g postgres postgres
id postgres

结果:

uid=501(postgres) gid=501(postgres) 组=501(postgres)

(6)创建postgresql数据库的数据主目录并修改文件所有者

cd /pgsql/postgresql
mkdir data
chown postgres:postgres data
ls -al

【PostgreSQL】安装和常用命令教程

(7)配置环境变量

进入home/postgres目录可以看到.bash_profile文件。

cd /home/postgres
ls -al

【PostgreSQL】安装和常用命令教程

修改:

vim .bash_profile

使其生效

source .bash_profile 

(8)切换用户到postgres并使用initdb初使用化数据库

su - postgres
initdb

可以看到 /pgsql/postgresql/data已经有文件:

cd /pgsql/postgresql/data
ls

【PostgreSQL】安装和常用命令教程

(9)配置服务

修改/pgsql/postgresql/data目录下的两个文件。

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

  • pg_hba.conf 配置对数据库的访问权限。

    vim postgresql.conf
    
    listen_addresses = '*'
    

    把这个地址改为*,表示在本地的所有地址上监听

    vim pg_hba.conf
    

    在# IPv4 local connections:下添加:

    host    all             all             0.0.0.0/0                  trust
    

    (10)设置PostgreSQL开机自启动

    PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下。

    cd /root/pgsql/postgresql-15.3/contrib/start-scripts
    

    【PostgreSQL】安装和常用命令教程

    添加执行权限:

    chmod a+x linux
    

    复制linux文件到/etc/init.d目录下,更名为postgresql:

    cp linux /etc/init.d/postgresql
    

    修改/etc/init.d/postgresql文件的两个变量:

    # Installation prefix
    prefix=/pgsql/postgresql
    # Data directory
    PGDATA="/pgsql/postgresql/data"
    

    设置postgresql服务开机自启动

    chkconfig --add postgresql
    

    查看:chkconfig

    【PostgreSQL】安装和常用命令教程

    编辑/etc/sysconfig/iptables文件开放5432端口:

    cd /etc/sysconfig
    vim iptables
    

    添加下列内容:

    -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT
    

    重启服务,或者其它命令:

    systemctl restart iptables.service
    

    查看端口是否开放:

    /sbin/iptables -L -n
    

    执行service postgresql start,启动PostgreSQL服务:

    service postgresql start
    

    【PostgreSQL】安装和常用命令教程

    查看PostgreSQL服务:

    ps -ef | grep postgres
    

    如果没有iptables:

    systemctl stop firewalld.service && systemctl disable firewalld.service
    yum install iptables-services iptables-devel
    systemctl enable iptables.service && systemctl start iptables.service
    
    查看状态 systemctl status iptables.service
    查看iptables配置文件 vim /etc/sysconfig/iptables
    

    psql失败:

    psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: Connection refused
    	Is the server running locally and accepting connections on that socket?
    
    tail -f /pgsql/postgresql/data/pg_log/postgresql-*.log
    

    netstat -nlp | grep 5432后没有任何信息,说明postgresql服务没有顺利启动。

    PostgreSQL linux安装教程2——centos7

    centos7安装PG

    centos7安装PG2

    centos7安装PG3

    (1)进入官方下载https://www.postgresql.org/download/

    选择对应的系统和对应的版本,注意,版本不要太高:

    【PostgreSQL】安装和常用命令教程

    PG11安装命令:

    # Install the repository RPM:
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    # Install PostgreSQL:
    sudo yum install -y postgresql11-server
    # Optionally initialize the database and enable automatic start:
    sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
    sudo systemctl enable postgresql-11
    sudo systemctl start postgresql-11
    

    创建数据库测试:

    切换到postgres用户

    su - postgres
    
    psql 
    

    创建用户

    create user test with password '123.com';
    

    创建数据库

    create database test_db owner test;
    

    退出

    \q
    

    (2)配置远程连接

    修改文件postgresql.conf :

    vim /var/lib/pgsql/11/data/postgresql.conf
    

    添加:

    listen_addresses = ‘*’
    

    修改pg_hba.conf文件:

    vim /var/lib/pgsql/11/data/pg_hba.conf
    

    添加:

    host all all 0.0.0.0/0 trust
    

    (3)重启并关闭防火墙

    重启服务:

    systemctl restart postgresql-11.service
    

    关闭防火墙:

    systemctl stop firewalld
    

    注意,

    • 如果是腾讯云/阿里云,需要配置端口开放,很多时候卡在这。
    • 防火墙没关闭完全
    • 连接软件全都关闭试试

      (4)验证

      可以通过navicat来尝试能否连接hsang

      PG常用指令

      PG数据库教程

      查看数据库
      \l
      
      使用数据库
      \c imdbload
      
      查看表
      \dt
      
      查看索引
      \di
      
      执行sql脚本
      \i /var/lib/pgsql/benchmark/schematext.sql;
      
      生成执行计划
      EXPLAIN (FORMAT JSON, analyze)
      SELECT MIN(mc.note) AS production_note,
             MIN(t.title) AS movie_title,
             MIN(t.production_year) AS movie_year
      FROM company_type AS ct,
           info_type AS it,
           movie_companies AS mc,
           movie_info_idx AS mi_idx,
           title AS t
      WHERE ct.kind = 'production companies'
        AND it.info = 'top 250 rank'
        AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
        AND (mc.note LIKE '%(co-production)%'
             OR mc.note LIKE '%(presents)%')
        AND ct.id = mc.company_type_id
        AND t.id = mc.movie_id
        AND t.id = mi_idx.movie_id
        AND mc.movie_id = mi_idx.movie_id
        AND it.id = mi_idx.info_type_id;
      
      创建物化视图
      CREATE MATERIALIZED VIEW mv2 AS
      select info_type.id AS id, info_type.info AS info, movie_info_idx.movie_id AS movie_id
       from movie_info_idx,info_type
       where (movie_info_idx.info_type_id = info_type.id) And (info_type.info = 'top 250 rank');
      
      EXPLAIN (FORMAT JSON, analyze)
      select info_type.id AS id, info_type.info AS info, movie_info_idx.movie_id AS movie_id
       from movie_info_idx,info_type
       where (movie_info_idx.info_type_id = info_type.id) And (info_type.info = 'top 250 rank');
      
      EXPLAIN (FORMAT JSON, analyze)
      SELECT MIN(t.title) AS movie_title
      FROM company_name AS cn,
           movie_companies AS mc,
           title AS t,
           mv12
      WHERE cn.country_code ='[us]'
        AND cn.id = mc.company_id
        AND mc.movie_id = t.id
        AND t.id = mv12.movie_id
        AND mc.movie_id = mv12.movie_id;
      
      
      postgreSQL取某个表的前100行作为一个新表
      CREATE TABLE new_table AS
      SELECT * FROM original_table
      LIMIT 100;
      
      暂停PG运行
      sudo systemctl stop postgresql-11
      
      设置PG并行执行相关
      set max_parallel_workers_per_gather = 0;
      

      表示禁用每个 Gather 操作的并行执行。

      如果您需要禁用全局并行查询,您还需要将 max_parallel_workers 参数设置为 0

      关闭Memoize算子
      set enable_memoize TO off;
      
      修改PG参数文件

      14是PG的版本名字

      /var/lib/pgsql/14/data
      

      可修改参数:

      (1)work_mem

      在 PostgreSQL 中,work_mem 参数控制每个工作进程使用的内存量。该参数设置得越高,可以执行更复杂的查询,但是也将占用更多的系统资源和内存。

      work_mem = 2GB
      

      (2)shared_buffers

      请注意,shared_buffers 参数控制着 PostgreSQL 使用的共享内存缓冲区的大小。设置得太小可能导致频繁的磁盘读写操作,而设置得太大可能会占用过多的系统内存。建议根据您的硬件配置和数据库负载进行适当的调整。

      shared_buffers = 1GB
      

      (3)effective_cache_size

      请注意,effective_cache_size 参数用于告诉 PostgreSQL 系统可用的内存缓存大小。设置得太小可能导致频繁的磁盘读写操作,而设置得太大可能会占用过多的系统内存。建议根据您的硬件配置和数据库负载进行适当的调整。

      effective_cache_size = 32GB
      

      (4)gego_threshold

      gego_threshold = 18
      
      查看配置文件路径
      show config_file;
      

      【PostgreSQL】安装和常用命令教程

      修改PG的data存储路径

      https://www.cnblogs.com/miracle-luna/p/17019256.html

      em` 参数控制每个工作进程使用的内存量。该参数设置得越高,可以执行更复杂的查询,但是也将占用更多的系统资源和内存。

      work_mem = 2GB
      

      (2)shared_buffers

      请注意,shared_buffers 参数控制着 PostgreSQL 使用的共享内存缓冲区的大小。设置得太小可能导致频繁的磁盘读写操作,而设置得太大可能会占用过多的系统内存。建议根据您的硬件配置和数据库负载进行适当的调整。

      shared_buffers = 1GB
      

      (3)effective_cache_size

      请注意,effective_cache_size 参数用于告诉 PostgreSQL 系统可用的内存缓存大小。设置得太小可能导致频繁的磁盘读写操作,而设置得太大可能会占用过多的系统内存。建议根据您的硬件配置和数据库负载进行适当的调整。

      effective_cache_size = 32GB
      

      (4)gego_threshold

      gego_threshold = 18
      
      查看配置文件路径
      show config_file;
      

      [外链图片转存中…(img-ojLvcnHW-1705414404781)]

      修改PG的data存储路径

      https://www.cnblogs.com/miracle-luna/p/17019256.html

VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]