# - Anything. - # # - - # # - known IP's - # uSH 24.46.78.201 uSO 72.76.115.67 # - PostgreSQL-HomePage - # https://www.postgresql.org/ https://www.youtube.com/watch?v=-LwI4HMR_Eg https://www.pgadmin.org/ https://tecadmin.net/install-postgresql-server-on-ubuntu/# https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04 https://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html http://yallalabs.com/linux/how-to-enable-network-remote-access-to-postgresql-database-server/ # - - # # - Very important - # https://www.tutorialspoint.com/postgresql/index.htm https://www.tutorialspoint.com/postgresql/postgresql_quick_guide.htm # - - # # - How to install pgAdmin 4 in server mode on Ubuntu - # https://askubuntu.com/questions/788457/how-to-install-pgadmin-4-in-server-mode-on-ubuntu-16-04 # - - # # - Very important book - # # - Essential PostgreSQL - Leo Hsu & Regina Obe - # https://dzone.com/refcardz/essential-postgresql # - - # # - Install PostgreSQL on Ubuntu Server - # sudo apt-get update; sudo apt-get install postgresql postgresql-contrib; # - Install PostgreSQL Client on other computer - # sudo apt-get update; sudo apt-get install postgresql-client; sudo apt install postgresql-client-common; # - Check status - # sudo systemctl status postgresql; service postgresql status; # - - # # - Configure path on Mac - # sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp # - - # # - Help in terminal - # man psql q to quit # - - # # - Shutdown firewall ubuntu server - # sudo ufw disable # - - # # - Files to configure - # # - Directory - # cd /etc/postgresql/9.6/main # - Backup of files - # sudo cp -p pg_hba.conf pg_hba.conf.old sudo cp -p postgresql.conf postfresql.conf.old pg_hba.conf pg_hba.conf.old postgresql.conf postfresql.conf.old # - - # # - Edit pg_hba.conf file - # sudo vim pg_hba.conf sudo vim /etc/postgresql/9.6/main/pg_hba.conf # - Look for line host___all - # ?host all # IPv4 local connections: host all all 127.0.0.1/32 md5 # - Add line - # host all all 192.168.1.0/24 md5 host all all 72.76.115.0/24 trust # IP Address in Office host all all 24.46.78.0/24 trust # IP Address at Home host all all * md5 #host all all 192.168.1.0/24 md5 # - - # # - Edit listening addresses on postgresql.conf file - # sudo vim postgresql.conf sudo vim /etc/postgresql/9.6/main/postgresql.conf # - Look for line listen_addresses - # #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' # - - # # - Older versions to activate port 5432 - # tcpip_socket = true sudo ufw allow 5432/tcp sudo service postgresql stop; sudo service postgresql start; sudo service postgresql restart; invoke-rc.d postgresql restart; sudo /etc/init.d/postgresql restart; sudo /etc/init.d/postgresql reload; sudo systemctl status postgresql; # - - # # - IpTables rule - # sudo iptables -t nat -I PREROUTING -p tcp --dport 5432 -j DNAT --to-destination 127.0.0.1 sudo iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 192.168.1.6 --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT sudo iptables -A OUTPUT -p tcp -s 192.168.1.6 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT # - - # # - Switch over to the postgres account on your server by typing: - # sudo -i -u postgres; sudo -i -u postgres -h 192.168.1.100; sudo -i -u postgres -h isdevelopmet.us; # - - # # - Access with password - # psql -d postgres -W psql -d postgres; psql -d postgres -h 192.168.1.100; psql -h 192.168.1.100 -U is_derayo -d postgres; psql -h isdevelopment.us -U postgres -d postgres; psql -h 192.168.1.100 -U postgres -d postgres; psql -p 5432 -d postgres -U postgres -h 192.168.1.100 psql -U is_derayo -h isdevelopment.us -p 5432 postgres psql -U postgres -h 192.168.1.100 -p 5432 -d postgres psql -U is_derayo -h 192.168.1.100 -p5432 -d postgres; psql -U is_derayo -h 24.46.78.201 -p5432 -d postgres; psql -h 72.76.115.67 -U postgres -d postgres; psql -h 192.168.1.100 -d postgres -U postgres -W psql -p 5432 -d postgres -U is_derayo -h localhost; psql -c "ALTER USER postgres WITH PASSWORD 'A22';" -d postgres; sudo -u postgres psql -c "ALTER USER is_derayo WITH PASSWORD 'A22';" -d postgres; sudo -u postgres psql -c "CREATE SCHEMA test AUTHORIZATION test;" test; ALTER USER postgres WITH PASSWORD 'A22'; # - Switch db connection - # At the PSQL prompt, you can do: \connect (or \c) dbname ALTER USER postgres WITH PASSWORD 'A22'; ALTER USER is_derayo WITH PASSWORD 'A22'; mysql: SHOW TABLES postgresql: \d postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; mysql: SHOW DATABASES postgresql: \l postgresql: SELECT datname FROM pg_database; mysql: SHOW COLUMNS postgresql: \d table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table'; mysql: DESCRIBE TABLE postgresql: \d+ table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table'; CREATE DATABASE testdb; CREATE DATABASE Accounting; ssh -L 63333:localhost:5432 isdevelopment.us ssh -L 63333:localhost:5432 is_derayo@isdevelopment.us ssh -L 63333:192.168.1.100:5432 is_derayo@isdevelopment.us You can now access a Postgres prompt immediately by typing: psql; Exit out of the PostgreSQL prompt by typing: \q postgres@u-ServerOffice:~$ exit # - - # # - Connection to PostgreSQL from Mac - # "/Applications/Postgres.app/Contents/Versions/9.6/bin/psql" -p5432 -d "postgres" -h 192.168.1.100 # - List databases - # \l # - List users - # \du Accessing a Postgres Prompt Without Switching Accounts: sudo -u postgres psql; sudo -i -u postgres psql; Create a New Role If you are logged in as the postgres account, you can create a new user by typing: createuser --interactive; If, instead, you prefer to use sudo for each command without switching from your normal account, you can type: sudo -u postgres createuser --interactive; The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications. Enter name of role to add: is_derayo Shall the new role be a superuser? (y/n) y You can get more control by passing some additional flags. Check out the options by looking at the man page: man createuser; # - - # # - From terminal - # sudo -u postgres psql postgres postgres=# \password postgres \password is_derayo; You'll see: Enter new password: Enter it again: You manipulate postgres through the user postgres, as so: # su - postgres $ createdb mydb $ psql -s mydb # create user someuser password 'somepassword'; # GRANT ALL PRIVILEGES ON DATABASE mydb TO someuser; # - Connecting from iMac terminal - # "/Applications/Postgres.app/Contents/Versions/9.6/bin/psql" -p5432 -d "postgres" # - - # # - Connecting in Linux with pgAdmin4.0 from web-browser - # https://askubuntu.com/questions/788457/how-to-install-pgadmin-4-in-server-mode-on-ubuntu-16-04 https://askubuntu.com/questions/831262/how-to-install-pgadmin-4-in-desktop-mode-on-ubuntu-16-04 sudo apt-get install virtualenv python-pip libpq-dev python-dev cd ~ virtualenv pgadmin4 cd pgadmin4 source bin/activate wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl pip install pgadmin4-1.5-py2.py3-none-any.whl sudo gedit lib/python2.7/site-packages/pgadmin4/config_local.py # Minimum configuration for config_local.py CSRF_SESSION_KEY = 'Change this now' SECRET_KEY = 'Change this now' SECURITY_PASSWORD_SALT = 'Change this now' SERVER_MODE = False To run cd ~/pgadmin4 source bin/activate python lib/python2.7/site-packages/pgadmin4/pgAdmin4.py # - - # # - pgbench - # Description pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files. https://www.postgresql.org/docs/9.5/static/pgbench.html # - - # # - pg_dump - # https://www.postgresql.org/docs/9.6/static/app-pgdump.html