http://pgcookbook.ru/article/could_not_connect_to_server_connection_refused.html
download
install guide
SHOW config_file;
# Then you need to alter these parameters inside PostgreSQL configuration file.
log_statement = 'all'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
logging_collector = on
log_min_error_statement = error
# restart
sudo apt-get install pgadmin3
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add
sudo apt update
sudo apt install pgadmin4
Добавляем пользователя
#add user
sudo -u postgres psql
ALTER USER postgres PASSWORD 'postgres';
\q
export PGPASSWORD='postgres'
psql -U postgres -h localhost
#restore
pg_restore -U postgres -h localhost -c -d DBNAME /path
# from sql srcipt
psql -U postgres -h localhost -d fias2 -a -f '/home/win/Рабочий стол/fias_history2.sql'
#dump
pg_dump volgrad | gzip > filename.gz
# with date
pg_dump -U postgres -h localhost -Fc volgrad -O > `date +/tmp/volgrad-%d-%m-%y.backup`
# some tables
pg_dump -U postgres -h localhost -t 'TABLE_NAME' -t accruals -Fc DBNAME -O > `date +/tmp/tbl-%d-%m-%y.backup`
# console command
\q
ctrl + D
# show DBS
\l
# connect
\c table
# decribe table
\dt table
\d+ table
# Describe Users
\du
# пример дампа
export PGPASSWORD='ПАРОЛЬ'
pg_dump -U remote-user -p 5432 -h 192.168.111.111 -Fc postgres -O > `date +/tmp/rem-exo-%d-%m-%y.backup`
pg_restore -U local-user -p 5432 -h localhost -c -d postgres /tmp/rem-ex
Удаленный доступ
/etc/postgresql/9.5/main/pg_hba.conf
И меняем параметр peer на md5 во всех строчках:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
/etc/postgresql/9.5/main/postgresql.conf
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
sudo service postgresql restart
# check
psql -Upostgres -h 192.168.5.33