§2023-04-04 - By ChatGPT To install PostgreSQL from source on Ubuntu 22.04, you can follow these steps: 1. Install the build dependencies: ```sql sudo apt-get update sudo apt-get install build-essential zlib1g-dev libreadline-dev libssl-dev ``` 2. nload the PostgreSQL source code from the official website: ```bash wget https://ftp.postgresql.org/pub/source/v14.0/postgresql-14.0.tar.gz wget https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2 ``` 3. Extract the source code: ```bash tar zxvf postgresql-14.0.tar.gz cd postgresql-14.0 ``` 4. Configure the build: ```javascript ./configure --prefix=/usr/local/pgsql --with-openssl --with-zlib ``` -This will configure the build to install PostgreSQL into the /usr/local/pgsql directory and enable support for OpenSSL and zlib. 5.Build and install PostgreSQL: ```go make sudo make install ``` - `time make -j6` - ```bash real 2m52.088s user 8m30.941s sys 0m55.710s ``` 5.1 Check ```bash $ /usr/local/pgsql/bin/psql --version psql (PostgreSQL) 15.2 $ ls /usr/local/pgsql/bin clusterdb dropdb initdb pg_basebackup pg_config pg_dump pg_receivewal pg_restore pg_test_timing pg_waldump psql createdb dropuser pg_amcheck pgbench pg_controldata pg_dumpall pg_recvlogical pg_rewind pg_upgrade postgres reindexdb createuser ecpg pg_archivecleanup pg_checksums pg_ctl pg_isready pg_resetwal pg_test_fsync pg_verifybackup postmaster vacuumdb ``` 6. Create the PostgreSQL user and data directory: ```bash sudo useradd -u 999 -g 999 -d /var/lib/postgres -m postgres # <-- as of ArchLinux $ sudo mkdir /var/lib/postgres/log alexlai@h2Jammy:~$ sudo chown -R postgres:postgres /var/lib/postgres/log/ sudo mkdir /var/lib/postgres/data sudo chown postgres:postgres //var/lib/postgres/data ``` 7. Initialize the database: ```bash $ sudo -iu postgres $ /usr/local/pgsql/bin/initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "C.UTF-8". The default text search configuration will be set to "english". Data page checksums are enabled. fixing permissions on existing directory /var/lib/postgres/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Taipei creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/local/pgsql/bin/pg_ctl -D /var/lib/postgres/data -l logfile start ``` 8. Start the PostgreSQL server: ---> please use postgresql as stated at the bottom ```wasm /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start ``` - This will start the PostgreSQL server and create a log file at logfile. 9. Verify that PostgreSQL is running: ```lua /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status ``` - This should output something like pg_ctl: server is running (PID: XXXX). --- - postgresql.service -If you make and install postgresql with `./configure --prefix=/usr/local/pgsql --with-openssl --with-zlib` , then how to write a postgresql.service file ```yaml [Unit] Description=PostgreSQL database server Documentation=man:postgres(1) [Service] Type=forking User=postgres Group=postgres ExecStart=/usr/local/pgsql/bin/pg_ctl start -D /var/lib/postgres/data -l /var/lib/postgres/log/postgresql.log -o '-p 5432' ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D /var/lib/postgres/data -l /var/lib/postgres/log/postgresql.log [Install] WantedBy=multi-user.target ``` - This service file assumes that the PostgreSQL data directory is located at /usr/local/pgsql/data, and the log file is located at /var/log/postgresql.log. If you have configured PostgreSQL differently, you may need to adjust these paths accordingly. - Reload the systemd daemon to load the new service file: ```bash sudo systemctl daemon-reload sudo systemctl enable postgresql.service sudo systemctl start postgresql.service sudo systemctl status postgresql.service ``` - verify ```bash $ sudo systemctl status postgresql ● postgresql.service - PostgreSQL database server Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2023-04-04 09:45:57 CST; 13s ago Docs: man:postgres(1) Process: 61773 ExecStart=/usr/local/pgsql/bin/pg_ctl start -D /var/lib/postgres/data -l /var/lib/postgres/log/postgresql.log -o -p 5432 (code=exited, status=0/SUCCESS) Main PID: 61776 (postgres) Tasks: 6 (limit: 9237) Memory: 16.0M CPU: 62ms CGroup: /system.slice/postgresql.service ├─61776 /usr/local/pgsql/bin/postgres -D /var/lib/postgres/data -p 5432 ├─61777 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ├─61778 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ├─61780 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ├─61781 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" └─61782 "postgres: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" 四 04 09:45:57 h2Jammy systemd[1]: Starting PostgreSQL database server... 四 04 09:45:57 h2Jammy pg_ctl[61773]: waiting for server to start.... done 四 04 09:45:57 h2Jammy pg_ctl[61773]: server started 四 04 09:45:57 h2Jammy systemd[1]: Started PostgreSQL database server. ``` --- - add user alexlai ```bash alexlai@h2Jammy:~$ sudo -iu postgres postgres@h2Jammy:~$ pwd /var/lib/postgres postgres@h2Jammy:~$ /usr/local/pgsql/bin/createuser --interactive Enter name of role to add: alexlai Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y postgres@h2Jammy:~$ psql Command 'psql' not found, but can be installed with: apt install postgresql-client-common Please ask your administrator. ``` ```bash postgres@h2Jammy:~$ /usr/local/pgsql/bin/psql psql (15.2) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+---------+---------+------------+-----------------+----------------------- postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (3 rows) postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alexlai | Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ALTER USER alexlai WITH PASSWORD 'alexnnnn'; postgres=# SET ROLE alexlai; SET postgres=> CREATE DATABASE alexlai; CREATE DATABASE postgres=> \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+---------+---------+------------+-----------------+----------------------- alexlai | alexlai | UTF8 | C.UTF-8 | C.UTF-8 | | libc | postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (4 rows) ``` - ~/.bashrc ```bash # postgresql export PATH=/usr/local/pgsql/bin:$PATH ```