# - - # # - - # # - known IP's - # uServerHome: www.isdevelopment.us # - 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 :Step 1 # - - # # - Installation in Server or Client (Don't do both procerures) - # :A # - - # # - Server - # sudo apt-get update && sudo apt-get install postgresql postgresql-contrib -y; :B # - - # # - Client - # sudo apt update; sudo apt install postgresql-client postgresql-contrib postgresql-client-common -y; Go to :Step 2 # - - # # - Don't use it - # # - Unless necessary - # # - Be very careful with this command - # # - Uninstall postgres totally - # sudo apt-get --purge remove postgresql* # - - # # - Install postgres 9.6.3 - # sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update # - - # # - Client only - # sudo apt-get update; sudo apt-get install postgresql-client postgresql-contrib postgresql-client-common; # - Server - # sudo apt-get update && sudo apt-get install postgresql postgresql-contrib; :Step 2 # - - # # - Check status - # sudo systemctl status postgresql; service postgresql status; Go to :Step 3 # - Done - # # - - # # - Configure path on Mac - # sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp # - - # # - Skip this on installation - # # - - # # - Help in terminal - # man psql \q to quit psql –help # - - # # - Execute an SQL script stored in a file - # psql –h localhost -U postgres –p 5432 –f /path/to/pgdumpall.sql # - - # # - Output data in html format - # psql -h someserver -p 5432 -U postgres -d dzone -H -c "SELECT * FROM pg_tips" -o tips.html # - - # # - Execute a single statement against a db - # psql -U postgres –p 5432 -d dzone -c "CREATE TABLE test(some_id serial PRIMARY KEY, some_text text);" # - - # # - Execute an SQL batch script against a database and send output to file - # psql -h localhost -U someuser -d dzone -f /path/to/ scriptfile.sql -o /path/to/outputfile.txt q Quit :q Cancel out of more screen \? Help on psql commands \h some_command Help on SQL commands \connect postgres Switch database \l List all databases \dtv p* List tables and views that start with p. \dn+ List of tables as well \dt *.* \dtv *.* \dt *.donor* \dt *.Chart* \dtv *.v_* \du List user/group roles and their group memberships and server level permissions. \d sometable List columns, data types, and constraints for a table. \i somefile Execute SQL script stored in a file. \o somefile Output contents to file. Use up and down arrows Retrieve prior commands. Linux: \! clear Windows: \! cls ARGUMENT DESCRIPTION -d Database. Defaults to the user (via system identification if no user is specified). -h Server host. Defaults to localhost if not specified. -p Port. Defaults to 5432 if not specified. -U Username you are trying to log in with. Defaults to system user name. # - - # # - \dt - # # - psql: no relations found despite existing relations - # https://stackoverflow.com/questions/17578077/psql-no-relations-found-despite-existing-relations https://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777 https://dba.stackexchange.com/questions/110550/why-cant-i-see-my-table-postgresql-when-i-use-dt-inside-psql https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-PATTERNS https://stackoverflow.com/questions/40865564/why-command-dt-gives-no-relations-found https://stackoverflow.com/questions/7758533/postgresql-database-owner-cant-access-database-no-relations-found/7758860#7758860 GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres; \dt *.* \dtv *.* \dt *.donor* # - - # # - Don't use - # # - Shutdown firewall ubuntu server - # sudo ufw disable :Step 3 # - - # # - Backup & edit files: pg_hba.conf & postgresql.conf - # :A # - - # # - Backup files - # systemctl status postgresql@11-main.service; systemctl status postgresql@12-main.service; sudo cp -p /etc/postgresql/12/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf.old sudo cp -p /etc/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf.old :B # - - # # - Edit pg_hba.conf - # cd /etc/postgresql/12/main sudo vim /etc/postgresql/12/main/pg_hba.conf host all all 127.0.0.1/32 md5 host all all 192.168.1.0/24 md5 :C # - - # # - Edit postgresql.conf - # sudo vim /etc/postgresql/12/main/postgresql.conf #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' :Step 4 # - - # # - Files owner, Restart postgres service, Change postgres password, Test access to DB - # :A # - - # # - If files owner changes - # cd /etc/postgresql/12/main; ls -lah; sudo chown postgres:postgres pg_hba.conf sudo chown postgres:postgres postgresql.conf ls -lah; :B # - - # # - Restart postgres service - # sudo service postgresql restart; sudo systemctl status postgresql; systemctl status postgresql@11-main.service; systemctl status postgresql@12-main.service; :C # - - # # - Change postgres password - # sudo bash; passwd postgres; Admin22 exit sudo -u postgres psql postgres; \password postgres Admin22 \q :D # - - # # - Test access to DB - # su postgres; psql \q exit sudo -i -u postgres; psql \q exit sudo -u postgres psql; \q sudo -u postgres psql postgres; \q # - Access from a client terminal - # sudo -u postgres psql -d postgres -h 192.168.1.100; psql -U postgres -d postgres -h www.isdevelopment.us; \q :Step 5 # - - # # - Create admin user, Test admin user - # :A # - - # # - Create admin user - # sudo -u postgres psql postgres; CREATE ROLE is_derayo WITH LOGIN PASSWORD 'Administrator2' SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS; ALTER USER is_derayo WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS; GRANT ALL PRIVILEGES ON DATABASE postgres TO is_derayo; GRANT ALL ON ALL TABLES IN SCHEMA public TO is_derayo; \q :B # - - # # - Test admin user - # sudo -u is_derayo psql -d postgres -h 192.168.1.100; sudo -u is_derayo psql -d postgres -h 192.168.1.110; \q :End # - - # # - End of PostgreSQL installation - # create user is_derayo password 'Administrator2'; create user is_derayo; \password is_derayo; create user mario; \password mario; You'll see: Enter new password: Enter it again: CREATE ROLE mario WITH LOGIN PASSWORD 'canario' SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS; GRANT ALL PRIVILEGES ON DATABASE postgres TO mario; GRANT ALL ON ALL TABLES IN SCHEMA public TO mario; DROP USER mario; \q # - - # # - From server's terminal - # sudo -u postgres psql -d postgres; psql -c "ALTER USER postgres WITH PASSWORD 'Admin22';" -d postgres; sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'postgres';" -d postgres; sudo -u postgres psql -c "ALTER USER is_derayo WITH PASSWORD 'Administrator2';" -d postgres; sudo -u postgres psql -c "CREATE SCHEMA test AUTHORIZATION test;" test; sudo -u postgres psql -d postgres -c "CREATE ROLE is_derayo WITH LOGIN PASSWORD 'Administrator2' SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;"; sudo -u postgres psql -d postgres -c "ALTER USER is_derayo WITH SUPERUSER CREATEROLE CREATEDB REPLICATION BYPASSRLS;"; sudo -u postgres psql -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE postgres TO is_derayo;"; sudo -u postgres psql -d postgres -c "GRANT ALL ON ALL TABLES IN SCHEMA public TO is_derayo;"; # - - # # - From ubuntu terminal - # sudo -u postgres psql -d postgres -h 192.168.1.100; sudo -u postgres psql -d postgres -h 192.168.1.200; # - - # # - If postgres connection fails - # sudo chown postgres:postgres pg_hba.conf sudo chown postgres:postgres postgresql.conf sudo service postgresql start; sudo service postgresql restart; sudo systemctl status postgresql; ps -ef | grep postgres pg_lsclusters pg_ctlcluster 10 main start; sudo systemctl start postgresql@10-main; sudo vim /var/log/postgresql/postgresql-10-main.log systemctl status postgresql@10-main.service; sudo service postgresql restart; sudo systemctl status postgresql; # - - # # - From another terminal - # # - In terminal - # psql -U postgres -d postgres -h 192.168.1.110 -c "ALTER USER postgres WITH PASSWORD 'postgres';"; psql -h 192.168.1.110 -d postgres -U is_derayo; psql -U is_derayo -d postgres -h 192.168.1.110; # - - # # - Show active user in postgres - # select current_user; SELECT CURRENT_USER; 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; # - - # # - 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; # - Switch db connection - # At the PSQL prompt, you can do: \connect (or \c) dbname ALTER USER postgres WITH PASSWORD 'A22'; ALTER USER postgres WITH PASSWORD 'Admin22'; ALTER USER is_derayo WITH PASSWORD 'A22'; GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres; 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; # - 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 SELECT CASE WHEN EXISTS (SELECT * FROM "Accounting"."ChartOfAccounts" WHERE Cia = 'IS' LIMIT 20) THEN 1 ELSE 0 END;