postgresql 기초 사용법 – 처음 사용해봄.
*작업환경
OS: ubuntu 22.04
PostgreSQL: 14.12
1. 데이터베이스 접속.
데이터베이스에 접속하기위해서는 postgres 계정을 이용한다. 리눅스 패키지로 설치한 경우에는 postgres 계정이 만들어져 있다.
su 명령으로 계정을 전환한다.
$ sudo su - postgres
데이터베이스에 접속하기 위해 psql 명령을 사용한다.
$ psql psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)) Type "help" for help. postgres=#
2. 데이터베이스 보기
현재 데이터베이스를 확인하려면, \l 명령을 사용한다. 이것은 mysql의 show databases와 비슷한 결과를 보여준다.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
3. 데이터베이스, 데이터베이스 사용자 만들기.
데이터베이스 만들기
snowfox라는 이름의 데이터베이스를 만든다.
postgres=# create database snowfox; CREATE DATABASE
확인해 보면 아래처럼 데이터베이스가 만들어졌다.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
snowfox | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
데이터베이스를 사용할 사용자 만들기.
snowfox라는 사용자를 만들고 비밀번호도 동일하게 만든다.
postgres=# create user snowfox with encrypted password 'snowfox'; CREATE ROLE
데이터베이스를 사용할 권한을 위에서 만든 사용자에게 부여한다.
postgres=# grant all privileges on database snowfox to snowfox; GRANT
4. 사용자 및 권한 확인.
\du 명령으로 사용자와 사용자의 권한을 확인한다.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
snowfox | | {}
postgres=# \du snowfox
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
snowfox | | {}
5. 위 사용자와 권한으로 데이터베이스 사용.
데이터베이스 접속.
postgres@foo:~$ psql -U snowfox -d snowfox -h localhost -p 5432 Password for user snowfox: psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. snowfox=>
test 테이블 생성.
snowfox=> create table test ( snowfox(> id serial primary key, snowfox(> name varchar(50), snowfox(> mail varchar(100) snowfox(> ); CREATE TABLE
snowfox=> insert into test (name, mail) values ('fox', 'foxx@test.com');
INSERT 0 1
snowfox=> select * from test; id | name | mail ----+------+--------------- 1 | fox | foxx@test.com (1 row)
테이블 보기
snowfox=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | test | table | snowfox
(1 row)
테이블 description 보기: \d 또는 \d+ 뒤에 테이블 이름을 붙인다.
snowfox=> \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+----------------------------------
id | integer | | not null | nextval('test_id_seq'::regclass)
name | character varying(50) | | |
mail | character varying(100) | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
테이블 삭제
snowfox=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------+----------+---------
public | test | table | snowfox
public | test_id_seq | sequence | snowfox
(2 rows)
snowfox=> drop table test; DROP TABLE
snowfox=> \d Did not find any relations.
6. 데이터베이스 접속 종료
snowfox-> \q
7. 데이터베이스 삭제
drop database 명령으로 삭제한다.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
snowfox | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | snowfox=CTc/postgres
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# drop database snowfox; DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
7. 백업 및 복구
백업은 pg_dump 명령을 사용한다.
$ pg_dump -U snowfox -d snowfox -h localhost -p 5432 Password: -- -- PostgreSQL database dump -- ... -- -- PostgreSQL database dump complete --
파일로 저장하는 옵션을 주지 않으면 화면으로 dump된다.
$ pg_dump -U snowfox -d snowfox -h localhost -p 5432 -F c -f snowfox.dmp Password:
-F c 옵션은 binary파일로 , -F p 는 plain text로 -F t는 tar 파일로 덤프된다.
데이터 베이스를 삭제하고, 복구해 본다.
복구는 pg_restore 명령을 사용한다.
$ pg_restore -U snowfox -d snowfox -h localhost -1 snowfox.dmp Password: pg_restore: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: database "snowfox" does not exist
데이터베이스를 삭제했기 때문에, 위와 같은 오류가 발생한다.
데이터베이스를 만들고, 다시 복구한다.
postgres@foo:~$ pg_restore -U snowfox -d snowfox -h localhost snowfox.dmp Password: