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 실행하여 생성한 유저로 로그인을 합니다.
댓글을 달려면 로그인해야 합니다.