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.
- Minimal FreeBSD 11 server. 8 GB RAM recommended.
- Sudo user with root privileges.
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.
You will see that the shell has changed to PostgreSQL shell.
Type "help" for help.
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:
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.
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.
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:
Activate the Virtual environment by typing:
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:
근데 요게 업그레이드가 가끔 되기 때문에 버전업을 하시려면
요기 사이트 가셔서 업그레이드 버전 상황보셔서 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:
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
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:
You will now be able to access the pgAdmin interface on http://your_server_IP:5050.
항상 실행하게 해 놓지 마시고 쓸때만 잠시 실행해서 쓰세요....
실행하실 때는 해당 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/