노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.

Postgres SQL - to_char가 응답이 늦을떄(Slow Query)


원문

select
    to_char("Timestamp",'YYYY/MM/DD HH24:MI:SS') as "Timestamp"
from 
    xxx
order by 1 asc


수정

select
    to_char("Timestamp",'YYYY/MM/DD HH24:MI:SS') as "Timestamp"
from (
    select "Timestamp" 
    from xxx
    order by 1 asc
)s


-- 변환 없이 쿼리를 하고, 원하는 포맷으로 감싸라 !!! ( 5배 빨라진다. )


참조 : https://stackoverflow.com/questions/24437023/postgresql-to-char-slowing-query-a-by-a-lot

'DBMS, noSQL > PostgreSQL' 카테고리의 다른 글

DB ERD 추출 하기  (0) 2017.10.27
dblink 문법  (0) 2017.10.25
Postgres 백업 및 복구 ( Backup & Restore with pgAdmin)  (0) 2017.07.21
재구매, 연속 구매  (0) 2017.04.12
Postgres CPU 과다 사용 ( high CPU Usage )  (0) 2017.01.23
블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.



DB ERD 추출 하기



* DbSchema 홈페이지 

http://www.dbschema.com/




* 특징


1. Interactive Diagrams


2. HTML 5 Documentation

: Table 정보를 Reverse Engineering으로 추출한 정보를 HTML5형식으로 저자


3. Schema Reverse Engineer

: DB 연결 정보를 입력하고, 대상을 선택하면, RE통해서 연관관계를 생성한 화면을 생성



* Evaluate 기간 : 15일

블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.


dblink 문법




예제



select 

uid 

from 

dblink('dbname=xxx port=5432 host=192.168.0.1 user=xxx password=xxxx',

'select uid from temp_table_a limit 10') 

as (uid int)

블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.


Postgres 백업 및 복구 ( Backup & Restore with pgAdmin)



옵션에 따라서 복구시에 에러가 발생하므로, 성공한 백업과 복구 옵션을 기록



1. 우측 메뉴 클릭, Backup  ( 선택된 Schema 전체 )



1) File Options

(1) Filename : 원하는 위치에 파일명을 선택

(2) Format : Custom 선택

(3) Rolename : 원하는 DB User선택


2) Dump Options #1

(1) Sections  : Pre-data, Data, Post-data를 모두 선택

(2) Type Of Objects : 선택 없음

(3) Don't save : Owner, Privilege만 선택


3) Dump Options #2

(1) Queries : Include CREATE DATABASE statement, Use Insert commands만 선택

(2) Disable : 선택 없음

(3) Miscellanous : 선택 없음


4) Objects

전체 선택


Backup 버튼 클릭


2. 우측 메뉴 클릭, Restore
: Database를 선택하거나 없는 경우에는 신규 생성후에 진행 



1) File Options

(1) Format : Custom 선택

(2) Filename : 백업된 파일을 선택

(3) Rolename : 원하는 DB User선택


2) Restore Options #1

(1) Sections  : Pre-data, Data, Post-data를 모두 선택

(2) Type Of Objects : 선택 없음

(3) Don't save : Owner, Privilege만 선택

3) Restore Options #2

선택 없음



Restore 버튼 클릭

'DBMS, noSQL > PostgreSQL' 카테고리의 다른 글

DB ERD 추출 하기  (0) 2017.10.27
dblink 문법  (0) 2017.10.25
재구매, 연속 구매  (0) 2017.04.12
Postgres CPU 과다 사용 ( high CPU Usage )  (0) 2017.01.23
Postgres , CSV를 Table로 import 하는 경우에 에러 발생  (0) 2016.12.06
블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.
재구매(매월 연속 구매)


- lag함수 , lead함수를 사용해서 발생 간격을 계산



1. 임시 데이타
CREATE TEMP TABLE transaction (buyer_id int, tstamp timestamp);
INSERT INTO transaction VALUES 
 (1,'2012-01-03 20:00')
,(1,'2012-01-05 20:00')
,(1,'2012-01-07 20:00')  -- multiple transactions this month
,(1,'2012-02-03 20:00')  -- next month
,(1,'2012-03-05 20:00')  -- next month
,(2,'2012-01-07 20:00')
,(2,'2012-03-07 20:00')  -- not next month
,(3,'2012-01-07 20:00')  -- just once
,(4,'2012-02-07 20:00'); -- just once


2. 쿼리(월별, 연속구매여부)

WITH t AS (
SELECT
buyer_id -- 구매 유저
,date_trunc('month', tstamp) AS month -- 구매 발생 월
,count(*) AS item_transactions -- 구매 회수
,lag(date_trunc('month', tstamp)) OVER (PARTITION BY buyer_id ORDER BY date_trunc('month', tstamp)) = date_trunc('month', tstamp) - interval '1 month' OR NULL AS repeat_transaction -- 이전 구매 발생이 1달전인가?
FROM
transaction WHERE
tstamp >= '2012-01-01'::date AND
tstamp < '2012-05-01'::date -- time range of interest. GROUP
BY 1, 2
) SELECT month ,sum(item_transactions) AS num_trans -- 거래수
,count(*) AS num_buyers -- 구매 유저수
,count(repeat_transaction) AS repeat_buyers -- 재구매 유저수
,round( CASE WHEN sum(item_transactions) > 0 THEN count(repeat_transaction) / sum(item_transactions) * 100 ELSE 0 END, 2) AS buyer_retention -- 재구매 비율
FROM t GROUP BY 1 ORDER BY 1;


결과
  month  | num_trans | num_buyers | repeat_buyers | buyer_retention_pct
---------+-----------+------------+---------------+--------------------
 2012-01 |         5 |          3 |             0 |               0.00
 2012-02 |         2 |          2 |             1 |              50.00
 2012-03 |         2 |          2 |             1 |              50.00



http://stackoverflow.com/questions/16492842/how-to-calculate-retention-month-over-month-using-sql


https://www.postgresql.org/docs/9.6/static/functions-window.html

http://stackoverflow.com/questions/17301816/lag-function-and-group-by

블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.

PostgreSQL CPU 과다 사용 ( high CPU Usage )




Postgres 프로세스중에서 CPU가 높은 PID를 찾고, 해당 PID를 통해서 Postgres ID를 찾아,

실행중인 query문장을 찾는다.






>Postgres(포스트 그레스) Backend ID(datid)를 System 프로세스 ID와 일치하는 정보는 아래 쿼리 결과를 통해서 실행된 query를 살펴 볼 수 있다.

SELECT pid, datname, usename, query FROM pg_stat_activity
where pid='';



> 실행 결과 예제 ( 구version )



http://dba.stackexchange.com/questions/44084/troubleshooting-high-cpu-usage-from-postgres-and-postmaster-services






* pg_stat_activity 속성

One row per server process, showing information related to the current activity of that process, such as state and current query. See pg_stat_activity for details.
ColumnTypeDescription
datidoidOID of the database this backend is connected to
datnamenameName of the database this backend is connected to
pidintegerProcess ID of this backend
usesysidoidOID of the user logged into this backend
usenamenameName of the user logged into this backend
application_nametextName of the application that is connected to this backend
client_addrinetIP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum.
client_hostnametextHost name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.
client_portintegerTCP port number that the client is using for communication with this backend, or -1 if a Unix socket is used
backend_starttimestamp with time zoneTime when this process was started, i.e., when the client connected to the server
xact_starttimestamp with time zoneTime when this process' current transaction was started, or null if no transaction is active. If the current query is the first of its transaction, this column is equal to the query_start column.
query_starttimestamp with time zoneTime when the currently active query was started, or if state is not active, when the last query was started
state_changetimestamp with time zoneTime when the state was last changed
waitingbooleanTrue if this backend is currently waiting on a lock
statetextCurrent overall state of this backend. Possible values are:
  • active: The backend is executing a query.

  • idle: The backend is waiting for a new client command.

  • idle in transaction: The backend is in a transaction, but is not currently executing a query.

  • idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.

  • fastpath function call: The backend is executing a fast-path function.

  • disabled: This state is reported if track_activities is disabled in this backend.

querytextText of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed.


블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.

ERROR: could not open file "xxx.csv"

Permission denied SQL state: 42501


-- PostgreSQL, CSV를 Table로 import 하는 경우에 발생


Table Import :

create table iptables
(
   ip varchar(50)
)

> copy iptables(ip) from C:\Users\denis\Desktop\import_local.csv delimiters ',' csv header


다음과 같은 Error 발생하는 경우 :

ERROR: could not open file "C:\Users\denis\Desktop\import_local.csv" for reading: Permission denied SQL state: 42501


해결책 :

C:\Program Files\PostgreSQL\9.5\scripts아래에 csv파일을 옮긴다.


그리고, Import 진행

1. 명령어 : 

copy iptables(ip) from 'C:\Program Files\PostgreSQL\9.5\scripts\import_local.csv' delimiters ',' csv header


2. pgAdmin 이용 :

https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table


추가 에러 : datestyle error

> show datestyle 

"ISO,YMD"

> set datestyle="ISO,MDY"

> copy temp.a_table(regdate)

from "c:\Program Files\PostgreSQL\9.5\scripts\aaa.csv' delimiter ',' csv header



블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.




ERD 그리기 & 리버스(Reverse)


- 도구 : SQL Power Architect v1.0.8



<도구 특징>


오픈소스, 무료 커뮤니티 버젼


Data Relation 자동 생성(Reverse Engineering) 및 편집 기능


작성된 ERD는 PlayPen Database로 구분


작성 ERD 내용은 프로젝트 형태로 저장 및 로딩 가능


DB연결은 JDBC 드라이버 사용


지원 DB : PostgreSQL, MS SQL Server, MySQL , HSQLDB




실행 화면 ( ERD 완료된 상태 )


: DB 연결 정보를 등록하면, 해당 DB의 Table정보를 가져오며, Drag & Drop으로 Table를 배치하고, 각 Table의 Relation을 맺어주면, 빠르게 ERD를 추출 가능하다.







Install후 실행


1. DB 연결 하기




2.  자동으로 로딩된 Schema/Table에서


1) Schema 전체를 선택하여 Drag & Drop으로 


-- 연결관계 있으면 자동 생성

-- 관계는 클릭시 속성 편집 가능





2) 필요한 Table만을 하나씩 Drag & Drop시에는


-- 각 테이블 선택후에 툴바에서  "새 비식별관계", "새 식별관계"를 선택

-- 원하는 필드를 A 테이블과 B 테이블을 누르면, 연결 속성이 생성됨





* 관련 Links  


- 소개  : SQL Power Architect : Data Modeling & Data Profiling Tool


- OS별 다운로드 : Open Source & free to download

블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.



pgAdmin III, 연결 정보 리스트 추출(복사, 백업)




1. regedit

2. HKEY_CURRENT_USER\SOFTWARE\pgAdmin III




3. save.reg 파일을 복원시에 실행

블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,
노출되는 이미지가 불편하시겠지만 양해를 구합니다. 노출, 클릭등에 관한 자료로 활용 중입니다.


postgres 설치하기 ( in CentOS )

 

 

 

 

PostgreSQL 설치와 DB 만들기

 

1. yum을 이용한 PostgreSQL 설치

>yum install postgreql-server 

2. 계정 확인

3. 데이터베이스 생성 경로 지정

4. initdb 실행

5. PostgreSQL 서버 실행

6. 사용자 DB 생성

 

 

 

 

CetnOS 7.0에서 PostgreSQL 외부 접속 허용하기


1. Password 설정하기


2. 외부접속 허용 설정하기


3. 방화벽 끄기

 

http://m.blog.naver.com/ships95/220237438650

 

 

 

 

 

일반 사용자 계정에 admin권한 부여하기


>sudo usermod -a -G sugo postgres 


http://askubuntu.com/questions/371832/how-can-run-sudo-commands-inside-another-user-or-grant-a-user-the-same-privileg

 

http://unix.stackexchange.com/questions/179954/username-is-not-in-the-sudoers-file-this-incident-will-be-reported



PostgreSQL 9.0 startup script for /etc/init.d


http://www.manniwood.com/2005_01_01/postgresql_startup_script_for_etcinitd.html



>postgresql start


블로그 이미지

StartGuide

I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.

,