'프로그램 사용 > postgreSQL' 카테고리의 다른 글
libpq (0) | 2019.06.24 |
---|---|
xsd to postgresql DDL (0) | 2019.06.18 |
pgadmin dashboard 살려내기 (0) | 2019.06.13 |
postresql backup & recovery (0) | 2019.06.12 |
postgresql schema (0) | 2019.06.12 |
libpq (0) | 2019.06.24 |
---|---|
xsd to postgresql DDL (0) | 2019.06.18 |
pgadmin dashboard 살려내기 (0) | 2019.06.13 |
postresql backup & recovery (0) | 2019.06.12 |
postgresql schema (0) | 2019.06.12 |
실수로 오른쪽의 X를 눌러 없앴는데
다시 살리는법 찾다가 도움되는건 못보고
걍 위에 우클릭하고 Add Panel 하니 다시 살릴수 있네...
xsd to postgresql DDL (0) | 2019.06.18 |
---|---|
postgresql dbms 설정 (0) | 2019.06.14 |
postresql backup & recovery (0) | 2019.06.12 |
postgresql schema (0) | 2019.06.12 |
postgres db 속도 향상 (0) | 2019.06.07 |
기존의 시스템에서 사용자가 설치한 postgresql 을 쓰고 있는데
이걸 리눅스 패키지에서 제공하는 녀석으로 파일 레벨에서 갈아 치우는 방법 찾는 중
(일일이 백업해서 옮기기 귀찮...)
[링크 : https://www.postgresql.org/docs/9.1/backup-file.html]
[링크 : https://stackabuse.com/backing-up-and-restoring-postgresql-databases/]
+
[링크 : https://h391106.tistory.com/352]
+
정 안되면
pg_dumpall | gzip > backup.gz
식으로 한번 뺴봐야 할 듯
[링크 : https://www.postgresql.org/docs/9.1/backup-dump.html]
+
[링크 : https://www.opsdash.com/blog/postgresql-backup-restore.html]
postgresql dbms 설정 (0) | 2019.06.14 |
---|---|
pgadmin dashboard 살려내기 (0) | 2019.06.13 |
postgresql schema (0) | 2019.06.12 |
postgres db 속도 향상 (0) | 2019.06.07 |
postgresql drop all tables (0) | 2019.06.05 |
pgadmin 으로 보다 보니 database > schema > table 순서로 냐오는게 있어서 찾는중
기본은 public schema인데 이게 머하는거지?
DB는 하나 이상의 'named schemaf'를 포함함.
일단은.. 귀찮으면 기본으로 제공되는 public 하나만 쓰고 테이블 별로 권한을 주고 싶으면
schema를 생성해서 테이블별로 사용자 권한을 주면 될 듯.
A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators.
There are several reasons why one might want to use schemas:
database.schema.table
Thus, the following are equivalent: |
[링크 : https://www.postgresql.org/docs/9.1/ddl-schemas.html]
스키마는 테이블들의 named collection 이다.
A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. |
[링크 : https://www.tutorialspoint.com/postgresql/postgresql_schema.htm]
[링크 : http://www.postgresqlforbeginners.com/2010/12/schema.html]
pgadmin dashboard 살려내기 (0) | 2019.06.13 |
---|---|
postresql backup & recovery (0) | 2019.06.12 |
postgres db 속도 향상 (0) | 2019.06.07 |
postgresql drop all tables (0) | 2019.06.05 |
postgresql import from csv (0) | 2019.06.04 |
의외로 postgres 서버의 메모리 늘리는 것은 크게 영향이 없는 듯 하고
설정 파일에서 이것저것 고친거랑.. cpu 갯수 늘린게 영향을 주려나?
일단은 확실한 벤치를 한건 아니지만 ramdom_page_cost 늘린게 가장 큰 영향을 주는 듯
work_mem As I mentioned earlier, memory allocation and management is a big part of performance-tuning PostgreSQL. If your system is doing a lot of complex sorts, increasing the sort memory can help the database optimize its configuration for your setup. This allows PostgreSQL to cache more data in memory while it performs its sorting, as opposed to making expensive calls to the disk. random_page_cost This setting essentially is the amount of time that your optimizer should spend reading memory before reaching out to your disk. You should alter this setting only when you’ve done other plan-based optimizations that we’ll cover soon, such as vacuuming, indexing, or altering your queries and schema. These are just some of the optimizations you can make for database configurations, but there are plenty more. Now that you know how to tweak your database setup, let’s look at another area for investigation: vacuuming. |
[링크 : https://stackify.com/postgresql-performance-tutorial/]
shared_buffers = — Editing this option is the simplest way to improve the performance of your database server. The default is pretty low for most modern hardware. General wisdom says that this should be set to roughly 25% of available RAM on the system. Like most of the options I will outline here you will simply need to try them at different levels (both up and down ) and see how well it works on your particular system. Most people find that setting it larger than a third starts to degrade performance.
|
[링크 : https://www.revsys.com/writings/postgresql-performance.html]
postresql backup & recovery (0) | 2019.06.12 |
---|---|
postgresql schema (0) | 2019.06.12 |
postgresql drop all tables (0) | 2019.06.05 |
postgresql import from csv (0) | 2019.06.04 |
postgresql password chg (0) | 2019.06.04 |
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
[링크 : https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database]
postgresql schema (0) | 2019.06.12 |
---|---|
postgres db 속도 향상 (0) | 2019.06.07 |
postgresql import from csv (0) | 2019.06.04 |
postgresql password chg (0) | 2019.06.04 |
postgresql 계정은 있는데 로그인 안될때 (0) | 2019.03.20 |
아래는 DOS 스타일로 CSV 파일로 부터 원하는 테이블에 데이터를 넣는 예제
COPY persons(first_name,last_name,dob,email) FROM 'C:\tmp\persons.csv' DELIMITER ',' CSV HEADER; |
[링크 : http://www.postgresqltutorial.com/import-csv-file-into-posgresql-table/]
pgadmin4에서 이런걸 발견 못했는데 도대체 어디서 찾아야 하나..
Use the Import/Export data dialog to copy data from a table to a file, or copy data from a file into a table. The Import/Export data dialog organizes the import/export of data through the Options and Columns tabs. |
[링크 : https://www.pgadmin.org/docs/pgadmin4/dev/import_export_data.html]
postgres db 속도 향상 (0) | 2019.06.07 |
---|---|
postgresql drop all tables (0) | 2019.06.05 |
postgresql password chg (0) | 2019.06.04 |
postgresql 계정은 있는데 로그인 안될때 (0) | 2019.03.20 |
postgresql WAL? (0) | 2019.03.14 |
헐.. 이렇게 간단한 명려어가 존재한다니..
sudo -u postgres psql postgres # \password postgres Enter it again: # \q |
postgresql drop all tables (0) | 2019.06.05 |
---|---|
postgresql import from csv (0) | 2019.06.04 |
postgresql 계정은 있는데 로그인 안될때 (0) | 2019.03.20 |
postgresql WAL? (0) | 2019.03.14 |
postgresql encoding / collate (0) | 2019.03.08 |
pg_hba.conf 외에도, role을 바꾸어 주어야 하네?
ALTER ROLE "asunotest" WITH LOGIN; |
[링크 : https://stackoverflow.com/questions/35254786/postgresql-role-is-not-permitted-to-log-in]
postgresql import from csv (0) | 2019.06.04 |
---|---|
postgresql password chg (0) | 2019.06.04 |
postgresql WAL? (0) | 2019.03.14 |
postgresql encoding / collate (0) | 2019.03.08 |
postgresql 설치(centos) (0) | 2019.03.08 |
서버를 보다보니 뭔가가 주기적으로 IO를 일으키는게 보여서
프로세스 확인해보니 특정 VM
특정 VM에다가 iotop 깔아서 보니 아래 녀석이 튀어나온다.
% postgres: wal writer process % postgres: checkpointer process % postgres: autovacuum worker process dbname |
그래서 WAL이 먼가 찾아보니 Write Ahead Logging의 약자라고..
WAL을 쓰면 하드에 쓰는 주기가 줄어든다고 하는데..
[링크 : http://postgresql.kr/docs/9.6/wal-intro.html]
[링크 : https://www.postgresql.org/docs/9.6/wal-intro.html]
체크포인트는 더티를 쓰는 거고(write back에 가깝나?)
At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. |
[링크 : https://www.postgresql.org/docs/9.2/wal-configuration.html]
[링크 : https://www.postgresql.org/docs/8.2/wal-configuration.html]
얘는 garbage collector 인거 같네
VACUUM -- garbage-collect and optionally analyze a database |
[링크 : https://www.postgresql.org/docs/8.2/sql-vacuum.html]
[링크 : https://www.postgresql.org/docs/8.2/runtime-config-autovacuum.html]
[링크 : https://www.postgresql.org/docs/8.2/routine-vacuuming.html]
읽으려니 눈에 안들어 오네.. ㅠㅠ
[링크 : https://www.cybertec-postgresql.com/en/postgresql-writer-and-wal-writer-processes-explained/]
postgresql password chg (0) | 2019.06.04 |
---|---|
postgresql 계정은 있는데 로그인 안될때 (0) | 2019.03.20 |
postgresql encoding / collate (0) | 2019.03.08 |
postgresql 설치(centos) (0) | 2019.03.08 |
postgreSQL centos 패키지 (0) | 2019.02.01 |