FreeBSD postgresql96-server install

FreeBSD postgresql96-server install

 

FreeBSD Version : 11 

Postgresql Version EOL 확인 https://www.postgresql.org/support/versioning/

 

Port install

root@bsd11:~ # whereis postgresql96-server
postgresql96-server: /usr/ports/databases/postgresql96-server
root@bsd11:~ # cd /usr/ports/databases/postgresql96-server/ && make install clean

 

or

pkg install

root@bsd11:~ # pkg install postgresql96-server postgresql96-client

 

설치완료후 메세지

Installing postgresql96-server-9.6.10...
===> Creating groups.
Creating group 'postgres' with gid '770'.
===> Creating users
Creating user 'postgres' with uid '770'.

  =========== BACKUP YOUR DATA! =============
  As always, backup your data before
  upgrading. If the upgrade leads to a higher
  minor revision (e.g. 8.3.x -> 8.4), a dump
  and restore of all databases is
  required. This is *NOT* done by the port!
  ===========================================
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.

If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.

The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perform vacuum on all
databases nightly. Per default, it performs `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~pgsql/data/postgresql.conf.

If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.

Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default.  Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.

To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
        :lang=en_US.UTF-8:\
        :setenv=LC_COLLATE=C:\
        :tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf.

======================================================================

To initialize the database, run

  /usr/local/etc/rc.d/postgresql initdb

You can then start PostgreSQL by running:

  /usr/local/etc/rc.d/postgresql start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD's PostgreSQL port logs to syslog by default
    See ~pgsql/data/postgresql.conf for more info

NB. If you're not using a checksumming filesystem like ZFS, you might
    wish to enable data checksumming. It can only be enabled during
    the initdb phase, by adding the "--data-checksums" flag to
    the postgres_initdb_flags rcvar.  Check the initdb(1) manpage
    for more info and make sure you understand the performance
    implications.

======================================================================

To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf

===> SECURITY REPORT:
      This port has installed the following files which may act as network
      servers and may therefore pose a remote security risk to the system.
/usr/local/bin/postgres

      This port has installed the following startup scripts which may cause
      these network services to be started at boot time.
/usr/local/etc/rc.d/postgresql

      If there are vulnerabilities in these programs there may be a security
      risk to the system. FreeBSD makes no guarantee about the security of
      ports included in the Ports Collection. Please type 'make deinstall'
      to deinstall the port if this is a concern.

      For more information, and contact details about the security
      status of this software, see the following webpage:
https://www.postgresql.org/
===>  Cleaning for gmake-4.2.1_2
===>  Cleaning for gettext-runtime-0.19.8.1_1
===>  Cleaning for libiconv-1.14_11
===>  Cleaning for indexinfo-0.3.1
===>  Cleaning for gettext-tools-0.19.8.1
===>  Cleaning for autoconf-2.69_1
===>  Cleaning for m4-1.4.18,1
===>  Cleaning for texinfo-6.5,1
===>  Cleaning for help2man-1.47.6
===>  Cleaning for p5-Locale-gettext-1.07
===>  Cleaning for perl5-5.26.2
===>  Cleaning for autoconf-wrapper-20131203
===>  Cleaning for automake-1.16.1
===>  Cleaning for icu-62.1_2,1
===>  Cleaning for postgresql96-client-9.6.10
===>  Cleaning for pkgconf-1.4.2,1
===>  Cleaning for readline-7.0.3_1
===>  Cleaning for postgresql96-server-9.6.10
root@bsd11:/usr/ports/databases/postgresql96-server #

 

postgresSQL 설정

root@bsd11:~ # sysrc postgresql_enable=yes
postgresql_enable:  -> yes
root@bsd11:~ #

 

postgres initdb 실행 

root@bsd11:~ # service postgresql initdb
이 데이터베이스 시스템에서 만들어지는 파일들은 그 소유주가 "postgres" id로
지정될 것입니다. 또한 이 사용자는 서버 프로세스의 소유주가 됩니다.

데이터베이스 클러스터는 다음 로케일으로 초기화될 것입니다.
  COLLATE:  C
  CTYPE:    ko_KR.UTF-8
  MESSAGES: ko_KR.UTF-8
  MONETARY: ko_KR.UTF-8
  NUMERIC:  ko_KR.UTF-8
  TIME:     ko_KR.UTF-8
initdb: "ko_KR.UTF-8" 로케일에 알맞은 전문검색 설정을 찾을 수 없음
기본 텍스트 검색 구성이 "simple"(으)로 설정됩니다.

자료 페이지 체크섬 기능 사용 하지 않음

/var/db/postgres/data96 디렉터리 만드는 중 ...완료
하위 디렉터리 만드는 중 ...완료
max_connections 초기값을 선택하는 중 ...100
기본 shared_buffers를 선택하는 중... 128MB
사용할 동적 공유 메모리 관리방식을 선택하는 중 ... posix
환경설정 파일을 만드는 중 ...완료
부트스트랩 스크립트 실행 중 ... 완료
부트스트랩 다음 초기화 작업 중 ... 완료
자료를 디스크에 동기화 하는 중 ... 완료

경고: 로컬 연결의 인증 방법으로 "trust" 방식을 지정했습니다.
이 값을 바꾸려면, pg_hba.conf 파일을 수정하든지,
다음번 initdb 명령을 사용할 때, -A 옵션 또는 --auth-local,
--auth-host 옵션을 사용해서 인증 방법을 지정할 수 있습니다.

작업완료. 이제 다음 명령을 이용해서 서버를 가동 할 수 있습니다:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data96 -l 로그파일 start

root@bsd11:~ #

 

Postgresql 실행

root@bsd11:~ # service postgresql start
로그: stderr 쪽 로그 출력을 중지합니다.
힌트: 자세한 로그는 "syslog" 쪽으로 기록됩니다.
root@bsd11:~ #
root@bsd11:~ # sockstat -4 |grep -i postgre
postgres postgres   839   4  tcp4   *:5432                *:*
root@bsd11:~ #

 

postgresql.conf 수정

root@bsd11:~ # vi /var/db/postgres/data96/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # what IP address(es) to listen on;

 

인증 방법 변경 trust -> md5 변경

root@bsd11:~ # cp  /var/db/postgres/data96/pg_hba.conf /var/db/postgres/data96/pg_hba.conf.org
root@bsd11:~ # vi /var/db/postgres/data96/pg_hba.conf

변경전
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

변경후
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::1/128              md5

 

postgres user pass 변경

root@bsd11:~ # passwd postgres
Changing local password for postgres
New Password:
Retype New Password:
root@bsd11:~ #

 

portgres user 로 변경후 작업

root@bsd11:~ # su - postgres
$ psql
psql (9.6.10)
도움말을 보려면 "help"를 입력하십시오.

postgres=# help
PostgreSQL에 대한 명령행 인터페이스인 psql을 사용하고 있습니다.
사용법:  \copyright 저작권 정보
         \h SQL 명령 도움말
         \? psql 명령 도움말
         \g 또는 명령 끝에 세미콜론(;) 쿼리 실행
         \q 종료
postgres=# \q

 

portgresSQL user 생성

$ createuser --interactive
추가할 새 롤(role)이름: db_user
새 롤을 superuser 권한으로 지정할까요? (y/n) n
이 새 롤에게 데이터베이스를 만들 수 있는 권할을 줄까요? (y/n) y
이 새 롤에게 또 다른 롤을 만들 수 있는 권한을 줄까요? (y/n) y
$

 

portgresSQL db 생성

$ createdb new_db

psql 에서 실행

postgres=# ALTER USER db_user WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE

postgres=# GRANT ALL PRIVILEGES ON DATABASE new_db TO db_user;
GRANT

postgres-# \list
                               데이터베이스 목록
   이름    |  소유주  | 인코딩 | Collate |    Ctype    |      액세스 권한
-----------+----------+--------+---------+-------------+-----------------------
 db_user   | postgres | UTF8   | C       | ko_KR.UTF-8 |
 new_db    | postgres | UTF8   | C       | ko_KR.UTF-8 |
 postgres  | postgres | UTF8   | C       | ko_KR.UTF-8 |
 template0 | postgres | UTF8   | C       | ko_KR.UTF-8 | =c/postgres          +
           |          |        |         |             | postgres=CTc/postgres
 template1 | postgres | UTF8   | C       | ko_KR.UTF-8 | =c/postgres          +
           |          |        |         |             | postgres=CTc/postgres
(5개 행)

postgres-#

 

pgAdmin 설치

root@bsd11:~ # python2.7 -m ensurepip
root@bsd11:~ # pkg install py27-virtualenv
root@bsd11:~ # mkdir Workspace
root@bsd11:~ # cd Workspace/
root@bsd11:~/Workspace # virtualenv pgadmin4
New python executable in /root/Workspace/pgadmin4/bin/python2.7
Also creating executable in /root/Workspace/pgadmin4/bin/python
Installing setuptools, pip, wheel...done.
root@bsd11:~/Workspace # cd pgadmin4/bin/
root@bsd11:~/Workspace/pgadmin4/bin # chmod +x activate
root@bsd11:~/Workspace/pgadmin4/bin # ./activate

 

일반유저 작업 test / wheel Group

root@bsd11:~ # su - test

$ python2.7 -m ensurepip
Requirement already satisfied: setuptools in /usr/local/lib/python2.7/site-packages
Requirement already satisfied: pip in /usr/local/lib/python2.7/site-packages
$ virtualenv pgadmin4
New python executable in /usr/home/test/pgadmin4/bin/python2.7
Also creating executable in /usr/home/test/pgadmin4/bin/python
Installing setuptools, pip, wheel...done.
$ . pgadmin4/bin/activate
(pgadmin4) $

프롬프트가 (pgadmin4) $ 바뀌게 됩니다.

 

root user 에서 python dependencie 를 설치 및 pgadmin config 설정

root@bsd11:~ # pip install cryptography pyopenssl ndg-httpsclient pyasn1
root@bsd11:~ # pkg install py27-sqlite3
root@bsd11:~ # wget --no-check-certificate https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl
root@bsd11:~ # pip install pgadmin4-1.5-py2.py3-none-any.whl


root@bsd11:~ # su - test
$ python2.7 /usr/local/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: test@test.com
Password:
Retype password:
Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.

$ su - 
root@bsd11:~ # vi /usr/local/lib/python2.7/site-packages/pgadmin4/config.py
#DEFAULT_SERVER = 'localhost'
DEFAULT_SERVER = '0.0.0.0'

# The default port on which the app server will listen if not set in the
# environment by the runtime
DEFAULT_SERVER_PORT = 5050

 

일반유저에서 실행

$ python2.7 /usr/local/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://0.0.0.0:5050 in your browser.

 

pgAdmin4.py 실행하여 생성한 유저로 로그인을 합니다. 

 

댓글 남기기