About deploying PostgreSQL on Ubuntu servers and installing PGADMIN4 Administration Tools (Web Edition)

Enter directory: CD PGADMIN4 source bin/activate CD pgadmin4-1.6/

Start Pgadmin4:python web/pgadmin4.py

Pgadmin login account: [email protected] (set yourself)

Password: ambition!! (Self-setting)

Database: postgres Password 123456

One, Ubuntu server installation

Please refer to: http://www.linuxidc.com/Linux/2012-05/60147.html

Ii. installation of PostgreSQL

1, sudo apt-get install PostgreSQL installs the latest version of PostgreSQL

2. Change password alter user postgres with password ' 123456 ';

Third, server remote configuration/

1. Modify the Listening address

sudo vi/etc/postgresql/9.5/main/postgresql.conf

#listen_addresses = ' localhost ' comment removed and changed to Listen_addresses = ' * '

: wq! Save

2. Modify the IP segment of the accessible user

sudo vi/etc/postgresql/9.5/main/pg_hba.conf

Add at end of file: Host all 0.0.0.0 0.0.0.0 MD5, which means any IP connection is allowed

: wq! Save

3. Restart the database

Sudo/etc/init.d/postgresql restart

Reboot successful OK

4, install the SSH server.

Depending on the installation of Ubuntu, if you cannot connect remotely, install this server.

sudo apt-get install Openssh-server

Iv. installation of PGADMIN4 management tools

1. Install PIP installation package

# sudo apt-get install build-essential libssl-dev libffi-dev libgmp3-dev virtualenv python-pip libpq-dev python-dev
2.Update your software

sudo apt-get update
sudo apt-get dist-upgrade
3.Install virtualenv virtual environment

sudo pip install virtualenvwrapper
virtualenv pgadmin4
cd pgadmin4 /
source bin / activate
4. Download the source code package of pgAdmin4

wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.6/source/pgadmin4-1.6.tar.gz
tar xf pgadmin4-1.6.tar.gz
cd pgadmin4-1.6 /
5. Install dependent libraries

sudo apt-get install libpq-dev
pip install -r requirements.txt
6, install pgadmin4, enter the email address and password as prompted

(pgadmin4) witwall: ~ / pgadmin4 / pgadmin4-1.1 $ python web / setup.py
Email address: [email protected]
Password:
Retype password:
7.Run pgadmin4

python web / pgAdmin4.py
Output: Starting pgAdmin 4. Please navigate to http: // localhost: 5050 in your browser.
Note: only local access, port 5050, this is not what I want

8. Modify the configuration to make it accessible on the LAN

cat >> web / config_local.py << EOF
from config import *
DEFAULT_SERVER = ‘0.0.0.0’
DEFAULT_SERVER_PORT = 8080
EOF
9.Run pgadmin4 again

python web / pgAdmin4.py
Output: Starting pgAdmin 4. Please navigate to http://0.0.0.0:8080 in your browser.
Five, psql command

\ password: set password

\ q: exit

\ h: View the explanation of SQL commands, such as \ h select.

\ ?: View the list of psql commands.

\ l: List all databases.

\ c [database_name]: Connect to another database.

\ d: List all tables in the current database.

\ d [table_name]: List the structure of a table.

\ du: List all users.

\ e: Open a text editor.

\ conninfo: List information about the current database and connection.

Use of postgresql

(1) Switch to the postgres user under Linux: sudo su postgres

(2) Log in to the postgres database: psql postgres

(3) Create a database user in command mode: create user dbuser with password ‘123456’;

(4) Create a database in command mode: create databse exampledb with owner dbuser;

(5) In the command mode, grant all permissions of the database example to the dbuser: grant all privileges on databse exampledb to dbuser;

(6) Exit psql command line mode: \ q

(7) Log in to the database: psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432 Entered exampledb =>

The meanings of the parameters of the above command are as follows: -U specifies the user, -d specifies the database, -h specifies the server, and -p specifies the port.

(8) Basic operation instructions

# Create a new table CREATE TABLE user_tbl (name VARCHAR (20), signup_date DATE);

# Insert data INSERT INTO user_tbl (name, signup_date) VALUES (‘Zhang San’, ‘2013-12-22’);

# Select record SELECT * FROM user_tbl;

# Update data UPDATE user_tbl set name = ‘Li Si’ WHERE name = ‘Zhang San’;

# Delete record DELETE FROM user_tbl WHERE name = ‘li si’;

# Add field ALTER TABLE user_tbl ADD email VARCHAR. ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# Rename field ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# Delete field ALTER TABLE user_tbl DROP COLUMN email;

# Table renamed ALTER TABLE user_tbl RENAME TO backup_tbl;

# Delete table DROP TABLE IF EXISTS backup_tbl;

 

About deploying postgresql on ubuntu server and installing pgadmin4 management tools (web version)