Dr. Kuang's Utilities

postgreSQL

 
  • Jan 04, 2023
  • views
>

背景

pg_ctl 是一個用於啟動、停止， 或重啟 PostgreSQL 後端伺服器，及顯示伺服器的狀態的工具。

Synopsis
pg_ctl start | stop | reload | status | restart [-D data_dir]

-D data_dir
聲明該資料庫文件的文件系統位置。 如果忽略這個選項，使用環境變量 PGDATA。

Note: 使用此命令前，請先將使用者切換至PostgreSQL super user(postgres)。

啟動伺服器：
$ pg_ctl start

停止伺服器：
$ pg_ctl stop

重啟伺服器：
$ pg_ctl restart

顯示伺服器狀態：
$ pg_ctl status
pg_ctl: postmaster is running (pid: 15718)
Command line was:
/usr/bin/postmaster '-D' '/var/lib/pgsql/data' '-p' '5433' '-B' '128'

# - Connection Settings -

listen_addresses = 'localhost'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
superuser_reserved_connections = 3      # (change requires restart)
unix_socket_directories = '/tmp'        # comma-separated list of directories
                                        # (change requires restart)
unix_socket_group = ''                  # (change requires restart)
unix_socket_permissions = 0777          # begin with 0 to use octal notation
                                        # (change requires restart)
bonjour = off                           # advertise server via Bonjour
                                        # (change requires restart)
bonjour_name = ''                       # defaults to the computer name
                                        # (change requires restart)

sudo -u postgres createuser -P yck4139

舊版且非postgres（使用者名稱）所brew之系統修正

  • 以postgres身分執行brew install postgresql@14時，發現防寫限制，brew建議以sudo將各目錄轉換擁有者給postgres
sudo chown -R postgres /usr/local/Cellar /usr/local/Frameworks /usr/local/Homebrew /usr/local/bin /usr/local/etc /usr/local/etc/bash_completion.d /usr/local/include /usr/local/lib /usr/local/lib/pkgconfig /usr/local/lib/python3.7/site-packages /usr/local/opt /usr/local/sbin /usr/local/share /usr/local/share/aclocal /usr/local/share/doc /usr/local/share/info /usr/local/share/locale /usr/local/share/man /usr/local/share/man/man1 /usr/local/share/man/man3 /usr/local/share/man/man5 /usr/local/share/man/man7 /usr/local/share/man/man8 /usr/local/share/zsh /usr/local/share/zsh/site-functions /usr/local/var/homebrew/linked /usr/local/var/homebrew/locks /usr/local/var/log
  • 還有/usr/local/var/postgres目錄下內容為舊版（@13）資料庫、與新版不相容，需完全清除sudo rm -fr /usr/local/var/postgres;sudo mkdir /usr/local/var/postgresql@14。
  • 如此才能順利卸載、重灌
brew uninstall postgresql@14 
brew install postgresql@14
  • 創建資料庫initdb -D /usr/local/var/postgresql@14,目錄為之前清除後再建
  • initdb --locale=C -E UTF-8 -D /usr/local/var/postgresql@14 by Yvonne
  • start the database server using:

pg_ctl -D '/usr/local/var/postgresql@14' -l logfile start

  • createdb wiki
  • createuser -P wikijs依序輸入密碼（wikijsrocks, from eg.）並確認

editing config.yml

中文全文搜尋

Search