pg_copy_between_databases
Transfer data between PostgreSQL databases by specifying source and target connection strings and table name. Optionally truncate target table or filter data using a WHERE clause.
Instructions
Copy data between two databases
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sourceConnectionString | Yes | ||
| tableName | Yes | ||
| targetConnectionString | Yes | ||
| truncateTarget | No | ||
| where | No |
Implementation Reference
- src/tools/migration.ts:223-285 (handler)Core implementation of the pg_copy_between_databases tool: connects to source DB, selects data (with optional WHERE), disconnects, connects to target DB, optionally truncates, then inserts data in a transaction.async function executeCopyBetweenDatabases( input: CopyBetweenDatabasesInput, getConnectionString: GetConnectionStringFn ): Promise<{ tableName: string; rowCount: number }> { const { sourceConnectionString, targetConnectionString, tableName, where, truncateTarget } = input; const db = DatabaseConnection.getInstance(); // Use the singleton for both connections sequentially try { // --- Source Operations --- await db.connect(sourceConnectionString); let query = `SELECT * FROM "${tableName}"`; if (where) { query += ` WHERE ${where}`; } const data = await db.query<Record<string, unknown>[]>(query); if (data.length === 0) { await db.disconnect(); // Disconnect source if no data return { tableName, rowCount: 0 }; } await db.disconnect(); // Disconnect source before connecting to target // --- Target Operations --- await db.connect(targetConnectionString); if (truncateTarget) { await db.query(`TRUNCATE TABLE "${tableName}"`); } let importedCount = 0; await db.transaction(async (client: import('pg').PoolClient) => { for (const record of data) { const columns = Object.keys(record); if (columns.length === 0) continue; const values = Object.values(record); const placeholders = values.map((_, i) => `$${i + 1}`).join(', '); const insertQuery = ` INSERT INTO "${tableName}" (${columns.map(c => `"${c}"`).join(', ')}) VALUES (${placeholders}) `; await client.query(insertQuery, values); importedCount++; } }); return { tableName, rowCount: importedCount }; } catch (error) { throw new McpError(ErrorCode.InternalError, `Failed to copy data: ${error instanceof Error ? error.message : String(error)}`); } finally { // Ensure disconnection in normal flow; connect() handles prior disconnects if needed. // The connect method in DatabaseConnection already handles disconnecting if connected to a different DB. // So, a single disconnect here should be fine, assuming the last active connection was target. // If an error occurred mid-operation (e.g., after source connect, before target connect), // connect() for target would handle disconnecting from source. // If an error occurs after target connect, this disconnect handles target. await db.disconnect(); } }
- src/tools/migration.ts:214-220 (schema)Zod input schema defining parameters for the tool: source and target connection strings, table name, optional where clause, and truncate flag.const CopyBetweenDatabasesInputSchema = z.object({ sourceConnectionString: z.string(), targetConnectionString: z.string(), tableName: z.string(), where: z.string().optional(), truncateTarget: z.boolean().optional().default(false), });
- src/tools/migration.ts:287-304 (registration)Tool object definition and export, including wrapper execute function that validates input and calls the core handler.export const copyBetweenDatabasesTool: PostgresTool = { name: 'pg_copy_between_databases', description: 'Copy data between two databases', inputSchema: CopyBetweenDatabasesInputSchema, async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> { const validationResult = CopyBetweenDatabasesInputSchema.safeParse(params); if (!validationResult.success) { return { content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], isError: true }; } try { const result = await executeCopyBetweenDatabases(validationResult.data, getConnectionString); return { content: [{ type: 'text', text: `Successfully copied ${result.rowCount} rows to ${result.tableName}` }] }; } catch (error) { const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error)); return { content: [{ type: 'text', text: `Error copying data: ${errorMessage}` }], isError: true }; } } };
- src/index.ts:225-257 (registration)Registration of all tools including pg_copy_between_databases (as copyBetweenDatabasesTool) into the allTools array passed to the MCP server constructor.const allTools: PostgresTool[] = [ // Core Analysis & Debugging analyzeDatabaseTool, debugDatabaseTool, // Schema & Structure Management (Meta-Tools) manageSchemaTools, manageFunctionsTool, manageTriggersTools, manageIndexesTool, manageConstraintsTool, manageRLSTool, // User & Security Management manageUsersTool, // Query & Performance Management manageQueryTool, // Data Operations (Enhancement Tools) executeQueryTool, executeMutationTool, executeSqlTool, // Documentation & Metadata manageCommentsTool, // Data Migration & Monitoring exportTableDataTool, importTableDataTool, copyBetweenDatabasesTool, monitorDatabaseTool ];