programming

Postgresql

Current version is 10.5
username and password are ‘postgres’

Installation

#apt-get update
$#apt-get install postgresql postgresql-client

Server setup

$ip addr show //from server
192.168.1.7/24

$ip addr show //from client
192.168.1.9/24

https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

nano /etc/postgresql/11/main/pg_hba.conf

# Allow any user from host 192.168.12.10 to connect to database
# “postgres” if the user’s password is correctly supplied.
#
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.1.9/24 trust

  • Must reload configuration after this modification:

su - postgres
/usr/lib/postgresql/10/bin/pg_ctl reload

OR

root@cellar:/home/mbc# pg_ctlcluster 11 main restart

OR

SELECT pg_reload_conf(); from psql

  • Must modify postgresql.conf to listen for all addresses

# grep listen /etc/postgresql/10/main/postgresql.conf
listen_addresses = ‘localhost’

#nano /etc/postgresql/10/main/postgresql.conf

# grep listen /etc/postgresql/10/main/postgresql.conf
listen_addresses = ‘*’

Must restart server after this modification

#/usr/lib/postgresql/10/bin/pg_ctl restart -D “/etc/postgresql/10/main”
OR

root@cellar:/home/mbc# pg_ctlcluster 11 main restart

# 2021 March sudo systemctl stop postgresql@11-main to stop

Client setup

#apt-get update
#apt-get install postgresql-client

$ping 192.168.1.7

be sure to get a response

# psql -U postgres -h 192.168.1.7
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.
postgres=#

Extensions

Extensions can be loaded by superuser only.
Extensions can be used by loader only.
Make a regular user a superuser, load extension, revoke superuser status.

su postgres
psql
alter role user_name superuser;
#then create the extension as the user in a different screen
CREATE EXTENSION intarray;
#back to role swapping screen
alter role user_name nosuperuser;

DROP extension; to remove extension

Initialize users/schema

psql -U postgres -h 192.168.1.11 -d lndb

postgres is the superuser
usually there is a database postgres

to see all databases
SELECT datname FROM pg_database;

\du to show all users

Must login as superuser to create a schema

PGbouncer

Needed to terminate artanis persistent connections

Install libevent
Install pgbouncer

./config –prefix=/usr/local
make
#make install

 INSTALL  pgbouncer /usr/local/bin
 INSTALL  README.md /usr/local/share/doc/pgbouncer
 INSTALL  NEWS.md /usr/local/share/doc/pgbouncer
 INSTALL  etc/pgbouncer.ini /usr/local/share/doc/pgbouncer
 INSTALL  etc/userlist.txt /usr/local/share/doc/pgbouncer
 INSTALL  doc/pgbouncer.1 /usr/local/share/man/man1
 INSTALL  doc/pgbouncer.5 /usr/local/share/man/man5

pgbouncer runs on a different port, so start it up in a terminal each session

$pgbouncer -d ./syncd/pgbouncer.ini
$psql -U ln_admin -p 6432 -d lndb -h 127.0.0.1

note the port

[pgbouncer.ini] [lang:bash]
1
2
3
4
5
6
7
8
9
10
lndb = host=127.0.0.1 port=5432 dbname=lndb

                  [pgbouncer]
                  listen_port = 6432
                  listen_addr = 127.0.0.1
                  auth_type = trust
                  auth_file = /home/mbc/syncd/userlist.txt
                  logfile = pgbouncer.log
                  pidfile = pgbouncer.pid
                  admin_users = ln_admin
[userlist.txt] [lang:bash]
1
2
"ln_admin" "welcome"

init.el

;_____________________________________________________________________________
;;;;SQLi setup

(require ‘sqlup-mode)
;; Capitalize keywords in SQL mode
(add-hook ‘sql-mode-hook ‘sqlup-mode)
;; Capitalize keywords in an interactive session (e.g. psql)
(add-hook ‘sql-interactive-mode-hook ‘sqlup-mode)
;; Set a global keyword to use sqlup on a region
(global-set-key (kbd “C-c u”) ‘sqlup-capitalize-keywords-in-region)

(setq sql-postgres-login-params
‘((user :default “postgres”)
(database :default “postgres”)
(server :default “192.168.1.7”)
(port :default 5432)))

(add-hook ‘sql-interactive-mode-hook
(lambda ()
(define-key sql-mode-map ‘[f5] ‘sql-send-region)
(toggle-truncate-lines t)))

to launch: M-x sql-postgres

Share