잠시만 기다려 주세요

HOME   |  여기는?   |  바다물때표   |  여러가지 팁 (888)  |  추천 및 재미 (152)  |  자료실 (20)  |  끄적거림 (519)  |  시나리오 (760)  |  드라마 대본 (248)  |  

2020년 09월 22일
사람은 조금만 여유가 생기면 본성이 나오고 어리석어진다. 적당한 노동으로 스스로를 단련하고 항상 깨어 넘어지지 않도록 해야 한다. - 바다아이
 




    freebsd 서버

freebsd 서버 - How to Install PostgreSQL and pgAdmin4 on FreeBSD 11
이 름 : 바다아이   |   조회수 : 3166         짧은 주소 : https://www.bada-ie.com/su/?221591784418

postgresql 은 서버에 이미 설치되어 운용되어 있겠지만
그래도 일단 설치법 같이 올려 놓습니다. 자꾸 까먹어서... ^^;

pgadmin4 가 파이썬 기반으로 개발이 되어 있네요..
써 보니 편하고 좋아요.. 근데 phppgadmin 이 속도는 더 좋습니다. -.-; 파이썬이라 그런지 속도가 ^^;....
php, apache 설치 하기 싫으신 분들한테 딱입니다.
필요할 때만 실행파일 실행해서 쓰고 그 외에는 보안상 실행하지 마세요..
방화벽 5050 포트 꼭 확인하시고요... 안쓸때는 포트 닫아놓으시구요...
실행하는 순간 파이썬 자체로 서버가 5050 포트로 실행되고 실행취소하면 서버도 같이 죽습니다.
거의 로컬에서 개발해서 업로드 하는 게 많아서 pgadmin3 으로 만족하지만
외부접속 없이 실시간으로 잠시 쓰기에는 좋습니다.



PostgreSQL or in short Postgres, is an open source Object-Relational Database Management System (ORDBMS). It is in active development since 1996 and evolved to be one of the best ORDMS. It supports all the features of RDBMS along with support for object-oriented database models. Objects, classes and inheritance are directly supported in database schemas and in the query language. PostgreSQL can be used to host the database for a variety of applications ranging from small websites to large cloud based applications or data warehousing.
 

In this tutorial, we will learn how to install PostgreSQL and pgAdmin on a FreeBSD 11 server.
 

Prerequisite

  • Minimal FreeBSD 11 server. 8 GB RAM recommended.
  • Sudo user with root privileges.


Installing PostgreSQL

 

At first update the server’s repository information.
 

sudo pkg update
 

pkg is the built in package manager for FreeBSD. Next, download and install PostgreSQL server and client packages.
 

sudo pkg install postgresql96-server postgresql96-client
 

Press y to proceed with the installation. pkg will now find and install the specified packages along with the required dependencies.

Once the installation finishes, enable PostgreSQL to start automatically at boot time.
 

sudo sysrc postgresql_enable=yes
 

Alternatively, to start PostgreSQL at boot time, you can also add postgresql_enable="YES" at the end of the /etc/rc.conf using any of your favorite text editor.

Now initialize the database using the following command.
 

sudo service postgresql initdb
 

initdb creates a new postgresql database cluster which is a collection of databases that are managed by a single server instance.

You can now start the PostgreSQL database server using the following command.
 

sudo service postgresql start
 

PostgreSQL database server is now installed.
 

Enable Remote Connections and Password Authentications

 

If the database server is to be accessed remotely, you will need to make a few changes in PostgreSQL configuration. In this tutori, l we will be using nano editor. If you do not have nano installed, you can run:
 

sudo pkg install nano
 

Open the configuration file using nano editor.
 

sudo nano /var/db/postgres/data96/postgresql.conf
 

Scroll down to find the lines shown below. Uncomment listen_addresses and change its value from localhost to *. You can also change the default port from 5432 to any other value you want.
 

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -


listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/tmp'       # comma-separated list of directories


By default PostgreSQL allows everyone to connect to psql shell without authenticating the user who are already logged in as PostgreSQL user. When the database is publicly accessible, you will need to change the authentication method to something more secure. You can use md5 authentication method which uses encrypted password for authentication. Further, you can also specify the IP addresses which are allowed for authentication.
 

Open the pg_hba.conf file using your favorite text editor.
 

sudo nano /var/db/postgres/data96/pg_hba.conf
 

Change the method of authentication from trust to md5 for host type connection. Further, add the address of the client machine from which database is to be accessed. To allow all clients to use md5 based authentication, use 0.0.0.0/0 as address.
 

# 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



For above changes to take effect, you will need to restart the PostgreSQL server.
 

sudo service postgresql restart
 

Changing Password and Creating User

 

By default, PostgreSQL creates a user postgres and group postgres to maintain PostgreSQL instance. To set the password for postgres user, run the following command.
 

sudo passwd postgres
 

After changing the password, login as postgres user.
 

su - postgres
 

You can now enter the PostgreSQL shell using the following command.
 

psql
 

You will see that the shell has changed to PostgreSQL shell.
 

psql (9.6.3)
Type "help" for help.


postgres=#


You can now run SQL queries from this shell. To exit from the PostgreSQL shell, type \q or \quit.

You can create a new role interactively by typing:
 

createuser --interactive
 

The script will ask you to enter the username and whether the user is a superuser.
 

$ createuser --interactive
Enter name of role to add: new_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y


Alternatively, you can run:
 

createuser -sdrP new_user
 

The meaning of the parameters are as follows.
 

  • s: This user will be superuser.
  • d: This user can create new databases.
  • r: This user can create new roles.
  • P: Assign a password to this role.


The above command will ask for the password for the new_user also.

To create a new database, you can run the following command.
 

createdb new_db
 

Now switch to PostgreSQL shell using psql command. If there is no password for the user, provide the password of the newly created account:
 

ALTER USER new_user WITH ENCRYPTED PASSWORD 'password';
 

Now grant all privileges of the database to the newly created user by running the query:
 

GRANT ALL PRIVILEGES ON DATABASE new_db TO new_user;
 

Exit the editor by running \q.
 

Installing pgAdmin

 

pgAdmin is the most popular open source application for managing a PostgreSQL database server. pgAdmin provides a feature rich graphical user interface to easily manage the databases. It is written in Python and Javascript/jQuery. It is available in multiple environments like Linux, Windows, Unix and available in desktop and server mode.
 

In this tutorial, we will install pgAdmin in server mode on the same server on which we have installed PostgreSQl.

pip is a package manager for Python packages. It is used to install and manage Python packages and dependencies. If you are logged in as postgres user, you will need to switch to the normal user with sudo privileges.
 

su - sudo_user
 

To install pip on your system, run:
 

sudo python -m ensurepip
 

It is recommended to use virtualenv to create isolated Python environment. Virtualenv creates an environment with its own Python installation which does not support libraries with global or another virtual environment. Run the following command for installation of Virtualenv.
 

sudo pkg install py27-virtualenv
 

Now create a virtual environment for pgAdmin by running:
 

virtualenv pgadmin4
 

Activate the Virtual environment by typing:
 

. pgadmin4/bin/activate
 

You will see that shell has changed to (pgadmin4) $.

Install few Python dependencies by typing:
 

sudo pip install cryptography pyopenssl ndg-httpsclient pyasn1
 

cryptography package provides cryptographic tools, pyopenssl is the Python wrapper module for OpenSSL library. ndg-httpsclient used pyopenssl to provide enhanced HTTPS support and pyasn1 is python implementation of ASN.1. These packages are required for pgAdmin to work.

pgAdmin4 also requires SQLite package. SQLite is also a SQL supported database management system. Instead of requiring a separate server, SQLite stores the data in flat files on disk. Install Python SQLite3 package by typing:
 

sudo pkg install py27-sqlite3
 

Download the Python wheel package for pgAdmin by typing:

근데 요게 업그레이드가 가끔 되기 때문에 버전업을 하시려면

https://ftp.postgresql.org/pub/pgadmin/pgadmin4/

요기 사이트 가셔서 업그레이드 버전 상황보셔서 wget 명령으로 해당 버전업 파일을 아래처럼 받습니다.
그리고 똑같이 pip 으로 인스톨 하시면 자동으로 기존 버전은 삭제되고 새 버전이 설치 됩니다. 참고하세요..
현재 2.1 인가 나와 있네요.. 1.5 받지 마시고 최신 버전 받으세요...

 

wget --no-check-certificate https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl
 

You can always check for the link to the latest version of the application on pgAdmin download page.

Now install the package by running:
 

pip install pgadmin4-1.5-py2.py3-none-any.whl
 

You can now run the application using:
 

python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
 

On first run the above command will ask for your email address and password to create initial pgAdmin user.
 

(pgadmin4) $ python ./pgadmin4/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: your_email
Password: your_new_password
Retype password:
Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.


The above command will start the server on port 5050 will listen to localhost only. To access the server from outside the network, you will need to make some configuration change. Copy the pgAdmin configuration file.
 

sudo cp ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config.py ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py
 

Now edit the local copy of configuration file using your favorite editor.

nano 설치 하기 싫으시면 그냥 vi 에디터 쓰셔도 됩니다.
 

sudo nano ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py
 

Change the default server value from localhost to 0.0.0.0, if you want you can also change the port on which the application is listening.
 

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


Start the server again by typing:
 

python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
 

You will now be able to access the pgAdmin interface on http://your_server_IP:5050.


p.s
항상 실행하게 해 놓지 마시고 쓸때만 잠시 실행해서 쓰세요....
실행하실 때는 해당 pgadmin4 설치한 전 폴더에서  . pgadmin4/bin/activate 실행 후 그 다음
python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py 입니다.
방화벽 5050 꼭 여시고요... 안쓸때는 닫기... ^^;

출처 : https://www.howtoforge.com/tutorial/how-to-install-postgresql-and-pgadmin-on-freebsd-11/

| |


      1 page / 2 page
번 호 카테고리 제 목 이름 조회수
34 freebsd 서버 , , , growfs, gpart, disk resize 바다아이 237
33 freebsd 서버 , ... google cloud platform time error, ntp 바다아이 232
32 freebsd 서버 ps .... 바다아이 250
31 freebsd 서버 BSD PF & DDoS 바다아이 270
30 freebsd 서버 freebsd ntp , , ntpd 바다아이 242
29 freebsd 서버 geoipupdate 401 .. .. 바다아이 517
28 freebsd 서버 freebsd vnstat , Network Traffic Monitor 바다아이 788
27 freebsd 서버 aws lightsail freebsd proftpd hostname .. 바다아이 807
26 freebsd 서버 freebsd , connect port, LISTEN and ESTABLISHED 바다아이 2106
25 freebsd 서버 freebsd , 바다아이 2010
24 freebsd 서버 freebsd linux postfix . 바다아이 2410
23 freebsd 서버 freebsd, linux postfix , 바다아이 2133
22 freebsd 서버 freebsd nginx + php + mariadb ... npm install setup 바다아이 2755
21 freebsd 서버 freebsd service , . 바다아이 2297
현재글 freebsd 서버 How to Install PostgreSQL and pgAdmin4 on FreeBSD 11 바다아이 3167
19 freebsd 서버 freebsd LetsEncrypt Certbot ssl 바다아이 3065
18 freebsd 서버 OS FreeBSD 바다아이 3095
17 freebsd 서버 freebsd pkg repository . mirror, , repo, Repository, , 바다아이 3251
16 freebsd 서버 freebsd ... 바다아이 3228
15 freebsd 서버 freebsd , service 바다아이 3296
14 freebsd 서버 freebsd crontab ...... 바다아이 2856
13 freebsd 서버 freebsd ll ls color 바다아이 2874
12 freebsd 서버 freebsd , pkg port update 바다아이 3043
11 freebsd 서버 freebsd pkg-config error 바다아이 3071
10 freebsd 서버 freebsd fail2ban , 바다아이 3093
9 freebsd 서버 FreeBSD IP IP Alias , 바다아이 2881
8 freebsd 서버 freebsd free -m freecolor 바다아이 3217
7 freebsd 서버 freebsd webalizer geoip 바다아이 2946
6 freebsd 서버 freebsd postgresql 9.6 바다아이 3089
5 freebsd 서버 freebsd firewall pf , 바다아이 3674
| |








Copyright ⓒ 2001.12. bada-ie.com. All rights reserved.
이 사이트는 리눅스에서 firefox 기준으로 작성되었습니다. 기타 브라우저에서는 다르게 보일 수 있습니다.
[ FreeBSD + GoLang + PostgreSQL ]
서버위치 : 구글 클라우드 플랫폼  실행시간 : 0.27463
to webmaster... gogo sea. gogo sea.