데이터베이스 연결

Error: remaining connection slots are reserved for non-replication superuser connections

Error: too many connections for database

const db = createKnexInstance(config);

// 여러 작업 수행
await db.raw("SELECT ...");
await db.raw("UPDATE ..."); // 여기서 에러 발생

await db.destroy(); // 도달하지 못함

const db = createKnexInstance(config);

try {
  await db.raw("SELECT ...");
  await db.raw("UPDATE ...");
  return result;
} finally {
  await db.destroy(); // 에러 발생 여부와 관계없이 실행
}

const sourceDB = createKnexInstance(sourceConfig);
const targetDB = createKnexInstance(targetConfig);

try {
  // 작업 수행
  const data = await sourceDB.select("*").from("users");
  await targetDB.insert(data).into("users");
  return data;
} finally {
  await targetDB.destroy();
  await sourceDB.destroy();
}

SELECT
  datname,
  count(*) as connections,
  max_conn
FROM pg_stat_activity
JOIN pg_database ON pg_stat_activity.datname = pg_database.datname
WHERE pg_database.datname = 'your_database_name'
GROUP BY datname, max_conn;

Error: connect ECONNREFUSED 127.0.0.1:5432

# macOS (Homebrew)
brew services list
brew services start postgresql

# Linux (systemd)
sudo systemctl status postgresql
sudo systemctl start postgresql

DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=your_password
DB_NAME=your_database

psql -h localhost -p 5432 -U postgres -d your_database

Error: Timeout acquiring a connection. The pool is probably full.

export default {
  database: {
    miomock: {
      host: "localhost",
      port: 5432,
      user: "postgres",
      password: "password",
      database: "miomock",
      pool: {
        min: 2,
        max: 10, // 기본값보다 증가
        acquireTimeoutMillis: 30000, // 타임아웃 증가
        idleTimeoutMillis: 30000,
      },
    },
  },
} satisfies SonamuConfig;

-- 현재 실행 중인 쿼리 확인
SELECT
  pid,
  now() - query_start as duration,
  query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- 특정 쿼리 종료
SELECT pg_cancel_backend(pid);  -- 부드럽게 종료
SELECT pg_terminate_backend(pid);  -- 강제 종료

-- 오래된 트랜잭션 확인
SELECT
  pid,
  now() - xact_start as duration,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC;

Error: permission denied for table users

-- 데이터베이스 소유자 확인
SELECT d.datname, u.usename
FROM pg_database d
JOIN pg_user u ON d.datdba = u.usesysid
WHERE d.datname = 'your_database';

-- 스키마 권한 부여
GRANT ALL ON SCHEMA public TO your_user;

-- 테이블 권한 부여
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;

-- 시퀀스 권한 부여
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO your_user;

Error: database "miomock_test" already exists

Error: database "miomock_test" is being accessed by other users

# 모든 연결 종료 후 DB 삭제
psql -U postgres -c "
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'miomock_test' AND pid <> pg_backend_pid();
"

psql -U postgres -c "DROP DATABASE IF EXISTS miomock_test;"

{
  "scripts": {
    "test": "vitest run --pool=forks --poolOptions.forks.singleFork=true"
  }
}

// vitest.config.ts
export default defineConfig({
  test: {
    globalSetup: "./test/setup.ts",
    globalTeardown: "./test/teardown.ts",
  },
});

// test/teardown.ts
export default async function teardown() {
  await DB.clearTestTransaction();
  await DB.closeConnection();
}