postgresql - money168/cowsay GitHub Wiki

centos 7 install postgresql

postgresql port 5432

  1. EPEL (postgresql 9.2)
    yum install postgresql-server postgresql-contrib
  2. first setup
    postgresql-setup initdb
  3. start service
    systemctl start postgresql

#ๅฎ‰่ฃๅพŒๆœƒๆ–ฐๅขžpostgresไฝฟ็”จ่€…๏ผŒไฝฟ็”จpostgresๅˆ‡ๆ›ไฝฟ็”จ่€…๏ผŒไฝฟ็”จpostgresql
sudo -u postgres psql -c โ€œSELECT version();โ€
su โ€“ postgres
psql็™ปๅ…ฅpostgresqlๆŒ‡ไปคไป‹้ข
#็ขบ่ชversiom
SELECT version();
psql -c โ€œSELECT version();โ€
#ๆชขๆŸฅ็™ปๅ…ฅ่ณ‡่จŠ
\conninfo;
#็™ปๅ‡บ
\q
=========
#Creating PostgreSQL Role and Database
#Only superusers and roles with CREATEROLE privilege can create new roles.
#In the following example, we will create a new role named test a database named testdb and grant privileges on the database.

#Create a new PostgreSQL Role
create role test;

#Create a new PostgreSQL Database
create database testdb;

#Grant privileges
#To grant permissions to the test user on the database we created in the previous step, run the following query:
grant all privileges on database testdb to test;

#Enable remote access to PostgreSQL server
#By default, the PostgreSQL server listens only on the local interface 127.0.0.1. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = โ€˜*โ€™ in the CONNECTIONS AND AUTHENTICATION section.

vim /var/lib/pgsql/data/postgresql.conf
#If you are running PostgreSQL version 10, the path to the file is /var/lib/pgsql/10/data/postgresql.conf.

/var/lib/pgsql/data/postgresql.conf
-
#โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”

  1. CONNECTIONS AND AUTHENTICATION
    #โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”โ€”
  1. - Connection Settings -

listen_addresses = โ€˜*โ€™ # what IP address(es) to listen on;
-
#save the file and restart the PostgreSQL service with:

systemctl restart postgresql

#configure the server to accept remote connections by editing the pg_hba.conf file.
/var/lib/pgsql/data/pg_hba.conf
-

  1. TYPE DATABASE USER ADDRESS METHOD
  1. The user test will be able to access all databases from all locations using a md5 password
    host all root 0.0.0.0/0 md5
  1. The user test will be able to access only the testdb from all locations using a md5 password
    host testdb root 0.0.0.0/0 md5
  1. The user test will be able to access all databases from a trusted location (192.168.1.134) without a password
    host all root 192.168.1.134 trust
    -
    =====
โš ๏ธ **GitHub.com Fallback** โš ๏ธ