Postgresql安装

on centos7

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql15-server
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

on ubuntu/debian

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
sudo apt update -y
sudo apt install postgresql-17 postgresql-client-17 -y
sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo -u postgres psql -c "SELECT version();"

#配置远程访问
sudo vim /etc/postgresql/17/main/postgresql.conf
listen_addresses = '*'
sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/17/main/pg_hba.conf
sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/17/main/pg_hba.conf
sudo vim /etc/postgresql/17/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

sudo systemctl restart postgresql

on docker

vi docker-compose.yml
services:
  postgres_db: # 服务名称
    image: postgres:17-alpine # 指定镜像及其版本
    container_name: pg17 # 指定容器的名称
    environment:
      POSTGRES_PASSWORD: gaojinbo.com
      #POSTGRES_DB: default
    ports: # 端口映射
      - "5432:5432"
    volumes: # 数据持久化的配置
      - data:/var/lib/postgresql/data
      - log:/var/log/postgresql
    logging:
      options:
        max-size: "10m"
        max-file: "3"

volumes: # 数据卷
  data:
  log:

docker compose up -d
docker exec -it pg17 su - postgres -c "psql -d t_db"

安装扩展pg_parquet

# add GPG key to keyring
curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

# get debian codename, distro_codename=jammy, focal, bullseye, bookworm
sudo tee /etc/apt/sources.list.d/pigsty-io.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/pgsql/$(lsb_release -cs) $(lsb_release -cs) main
EOF

# refresh APT repo cache
sudo apt update
apt install postgresql-17-pg-parquet -y

#如果是docker运行的pg，只需要复制扩展文件到容器就行
dpkg -L postgresql-17-pg-parquet
#复制文件到docker下pg的对应目录
cp pg_parquet* /usr/share/postgresql/17/extension/
cp pg_parquet.so /usr/lib/postgresql/17/lib/pg_parquet.so
echo "shared_preload_libraries = 'pg_parquet'" >> data/postgresql.conf
docker restart pg17
docker exec -it pg17 su - postgres
#启用扩展
\c db4
CREATE EXTENSION pg_parquet;
\dx