라즈베리파이(우분투)에 postgreSQL 설치 및 설정

라즈베리파이에 ssh로 접근하는 과정은 생략

도커 컨테이너를 사용할까 했지만 우선은 네이티브로 설치한다.

 

 

1. 패키지 목록 업데이트

coffee@coffee-rasp:~$ sudo su
[sudo] password for coffee: 
root@coffee-rasp:/home/coffee# apt update
Hit:1 http://ports.ubuntu.com/ubuntu-ports noble InRelease
Get:2 http://ports.ubuntu.com/ubuntu-ports noble-updates InRelease [126 kB]
Hit:3 http://ports.ubuntu.com/ubuntu-ports noble-backports InRelease
Hit:4 http://ports.ubuntu.com/ubuntu-ports noble-security InRelease
Fetched 126 kB in 3s (36.7 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
192 packages can be upgraded. Run 'apt list --upgradable' to see them.
root@coffee-rasp:/home/coffee#

 

2. postgreSQL 설치

 

root@coffee-rasp:/home/coffee# sudo apt install postgresql postgresql-contrib
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm17t64 libpq5 libtypes-serialiser-perl postgresql-16
  postgresql-client-16 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
  postgresql-doc postgresql-doc-16
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm17t64 libpq5 libtypes-serialiser-perl postgresql
  postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common postgresql-contrib ssl-cert
0 upgraded, 13 newly installed, 0 to remove and 192 not upgraded.
Need to get 42.2 MB of archives.
After this operation, 175 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

//....
Get:1 http://ports.ubuntu.com/ubuntu-ports noble/main arm64 libjson-perl all 4.10000-1 [81.9 kB]
Get:2 http://ports.ubuntu.com/ubuntu-ports noble-updates/main arm64 postgresql-client-common all 257build1.1 [36.4 kB]
Get:3 http://ports.ubuntu.com/ubuntu-ports noble/main arm64 ssl-cert all 1.1.2ubuntu1 [17.8 kB]
///...
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Setting up postgresql-contrib (16+257build1.1) ...
Setting up postgresql (16+257build1.1) ...
Processing triggers for man-db (2.12.0-4build2) ...
Processing triggers for libc-bin (2.39-0ubuntu8.2) ...
Scanning processes...                                                                                                    
Scanning processor microcode...                                                                                          
Scanning linux images...                                                                                                 

Running kernel seems to be up-to-date.

The processor microcode seems to be up-to-date.

No services need to be restarted.

No containers need to be restarted.

No user sessions are running outdated binaries.

No VM guests are running outdated hypervisor (qemu) binaries on this host.
root@coffee-rasp:/home/coffee#

 

3. 설치된 postgreSQL 서비스 상태 확인

root@coffee-rasp:/home/coffee# sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
     Active: active (exited) since Fri 2024-12-20 17:59:05 KST; 1min 17s ago
   Main PID: 3449 (code=exited, status=0/SUCCESS)
        CPU: 12ms

Dec 20 17:59:04 coffee-rasp systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Dec 20 17:59:05 coffee-rasp systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.
root@coffee-rasp:/home/coffee#

구동 확인

 

4. postgres 계정으로 전환 (설치와 함께 기본적으로 postgres 사용자가 생성된다)

sudo -i -u postgres

#-i (initialize-login):
#로그인 셸을 시작
#해당 사용자의 환경으로 완전히 전환
#홈 디렉토리로 이동하고 환경변수도 해당 사용자의 것으로 설정

#-u [username] (user):
#지정한 사용자로 명령을 실행
#여기서는 'postgres' 사용자로 전환

root@coffee-rasp:/home/coffee# sudo -i -u postgres
postgres@coffee-rasp:~$

 

변경된 것을 확인할 수 있다.

 

 

5. postgreSQL 프롬프트 접속

psql

postgres@coffee-rasp:~$ psql
psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

 

 

6. DB생성 - 유저 추가 - 권한 부여

-- 임의의 디비명(예시에서는 test_db)으로 디비 생성(언더바("_")를 사용하는 스네이크 케이스가 권장됨)
CREATE DATABASE test_db;

-- 임의의 유저명과 비밀번호로 사용자 생성(예시에서는 test_user, test_password) 
CREATE USER test_user WITH ENCRYPTED PASSWORD 'test_password';

-- 생성한 유저에게 디비에 관한 모든 권한을 부여
GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;

-- test_db 데이터베이스에 먼저 접속
\c test_db

-- public 스키마에 대한 권한 부여
GRANT ALL ON SCHEMA public TO test_user;

-- 향후 생성될 테이블에 대한 권한도 미리 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO test_user;

-- mysql 계열과 다르게 flush all privileges를 해줄 필요가 없다. 즉시 적용된다.

 

예시)

postgres=# CREATE DATABASE test_db;
CREATE DATABASE #성공하면 메시지 반환

postgres=# CREATE USER test_user WITH ENCRYPTED PASSWORD 'test_password';
CREATE ROLE
postgres=#

postgres=# GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user;
GRANT

postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# 
test_db=# 
test_db=# 
test_db=# GRANT ALL ON SCHEMA public TO test_user;
GRANT
test_db=# 
test_db=# 
test_db=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO test_user;
ALTER DEFAULT PRIVILEGES
test_db=#

 

 

7.  기타 명령어

 

목록을 보고 싶었는데 mysql 계열과 명령어가 달라서 애먹었다. 자주 사용하는 명령어를 정리해 보았다.

( + 추가: 추후 알게되었지만 표준 sql도 동작한다!!)

-- 화면 지우기 (MySQL의 clear나 cls가 아닌)
\! clear

-- 데이터베이스 목록 보기 (MySQL의 SHOW DATABASES가 아닌)
\l
또는 
\list

-- 데이터베이스 선택/전환 (MySQL의 USE database_name이 아닌)
\c database_name

-- 테이블 목록 보기 (MySQL의 SHOW TABLES가 아닌)
\dt

-- 메타 명령어(\로 시작하는) 도움말
\?

-- SQL 명령어 도움말
\h

-- 현재 연결 정보 보기 (MySQL의 status가 아닌)
\conninfo

 

유저를 확인하는 방법도 mysql계열과는 다르다.

mysql에서는 대부분 아래와 같은 방법으로 사용자를 확인한다.

SELECT user, host FROM mysql.user;

 

postgre에서는 아래와 같이 사용한다.

-- 방법 1: 메타 명령어 사용 (가장 간단)
\du
또는
\du+  (더 자세한 정보)

-- 방법 2: SQL 쿼리로 확인
SELECT usename AS role_name,
       CASE 
          WHEN usesuper AND usecreatedb THEN 
                CAST('superuser, create database' AS pg_catalog.text)
          WHEN usesuper THEN 
               CAST('superuser' AS pg_catalog.text)
          WHEN usecreatedb THEN 
               CAST('create database' AS pg_catalog.text)
          ELSE 
               CAST('' AS pg_catalog.text)
       END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name;

 

PostgreSQL은 MySQL과 달리 사용자 정보를 pg_user와 pg_roles 같은 시스템 카탈로그에 저장한다.

하지만 일반적으로는 \du 메타 명령어를 사용하는 것이 가장 편리하다고 한다.

 

 

+ 추가: 표준 sql로 테스트

 

-- 테이블 목록 확인
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

-- 테이블 생성
CREATE TABLE test_table (
    id serial PRIMARY KEY,
    name VARCHAR(100),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 데이터 삽입
INSERT INTO test_table (name) VALUES ('테스트1');
INSERT INTO test_table (name) VALUES ('테스트2');

-- 데이터 조회
SELECT * FROM test_table;


-- 테이블 삭제
DROP TABLE test_table;

 

 

 

 

8. 라즈베리 파이에서의 연결 설정

참고: postgreSQL의 설정파일은 postgreSQL.conf이다. 아래와 같이 접근할 수 있다.

sudo nano /etc/postgresql/16/main/postgresql.conf

 

테스트 디비이므로 모든 곳에서 접근할 수 있도록 변경한다.

# pg_hba.conf 파일 수정
sudo nano /etc/postgresql/16/main/pg_hba.conf


#맨 아래 다음의 내용 추가
host    all             all             0.0.0.0/0               scram-sha-256

#이렇게
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

host    all             all             0.0.0.0/0               scram-sha-256

 

sudo nano /etc/postgresql/16/main/postgresql.conf
# 해당 파일을 열어서

listen_addresses = '*'     # 기본값인 'localhost'를 '*'로 변경

이 부분을 '*'로 변경

 

그리고 postgreSQL 재시작

sudo systemctl restart postgresql

 

9. 연결 테스트

 

# 맥(인텔 맥)에 PostgreSQL 설치
brew install postgresql@16


#경로추가(인텔 맥. 애플실리콘의 경우 경로 다름)
echo 'export PATH="/usr/local/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc


# 변경사항 적용
source ~/.zshrc

# 설치 확인
psql --version
#psql (PostgreSQL) 16.6 (Homebrew)

 

연결 확인

psql (PostgreSQL) 16.6 (Homebrew)
 yongjuncha@Yongui-MacBookPro-2  ~  psql -h 20.20.20.21 -U test_user -d test_db
test_user 사용자의 암호: 
psql (16.6 (Homebrew))
SSL 연결정보 (프로토콜: TLSv1.3, 암호화기법: TLS_AES_256_GCM_SHA384, 압축: off)
도움말을 보려면 "help"를 입력하십시오.

test_db=>

 

vs코드의 확장프로그램에서 ckolkman이 만든 postgre 관리 프로그램을 설치하여 테스트해보았다.