Postgresql

postgres.md

Setup Postgressql LInux Debian

sudo apt update
sudo apt install postgresql
sudo apt install postgresql-client

# Setting up new password
sudo passwd postgres

user:~$ sudo -i -u postgres
postgres@user:~$ psql
postgres=# ALTER USER postgres PASSWORD 'mynewpassword';

Make it open to public.

sudo nano /etc/postgresql/9.3/main/postgresql.conf
# edit: listen_addresses = '*'
# save
sudo nano /etc/postgresql/9.3/main/pg_hba.conf
# add: host    all             all             192.168.7.0/24          md5
# save
sudo systemctl restart postgresql
# Check from another device using nmap
nmap <ip-adress> -p5432

Restore .tar file backup

pg_restore -c -U postgres -d client03 -v "/tmp/client03.tar" -W
# https://serverfault.com/questions/115051/how-to-restore-postgresql-database-from-tar-file

Restore .dump file backup

$ hexdump -C -n 200 exercises.dump 
00000000  50 47 44 4d 50 01 0e 00  04 08 01 01 01 00 00 00  |PGDMP...........|
00000010  00 12 00 00 00 00 0b 00  00 00 00 0e 00 00 00 00  |................|
00000020  06 00 00 00 00 02 00 00  00 00 78 00 00 00 00 00  |..........x.....|
00000030  00 00 00 00 06 00 00 00  65 78 74 65 73 74 00 04  |........extest..|
00000040  00 00 00 31 32 2e 32 00  04 00 00 00 31 32 2e 32  |...12.2.....12.2|
00000050  00 18 00 00 00 00 1b 0b  00 00 00 00 00 00 00 00  |................|
00000060  01 00 00 00 30 00 01 00  00 00 30 00 08 00 00 00  |....0.....0.....|
00000070  45 4e 43 4f 44 49 4e 47  00 08 00 00 00 45 4e 43  |ENCODING.....ENC|
00000080  4f 44 49 4e 47 00 02 00  00 00 00 1e 00 00 00 53  |ODING..........S|
00000090  45 54 20 63 6c 69 65 6e  74 5f 65 6e 63 6f 64 69  |ET client_encodi|
000000a0  6e 67 20 3d 20 27 55 54  46 38 27 3b 0a 01 01 00  |ng = 'UTF8';....|
000000b0  00 00 01 01 00 00 00 01  01 00 00 00 01 01 00 00  |................|
000000c0  00 01 01 00 00 00 01 01                           |........|
000000c8
$ # Format header is PGDUMP and mimum version is 12.2
$ # We need to create exercises database first.
$ user:~$ sudo -i -u postgres
postgres@user:~$ psql
postgres=# create database exercises;
postgres@user:~$ pg_restore -U postgres -d exercises -1 exercises.dump