postgreSQL schema export or extraction as operator

Introduction

This page explains how to export DDL from a PostgreSQL database for use with the SQL Analyzer extension.

Extraction example - Windows

  • Open a Command Prompt with “Run as Administrator” rights.
  • Switch to the PostgreSQL bin folder, e.g. :
cd "C:\Program Files\CAST\CASTStorageService2\bin"
  • Export a specific database:
REM pg_dump.exe -h <your postgres host> --username=operator --format plain --schema-only --schema=<your schema name> --port=<your postgres port> --file=<the name of the SQL (DDL) file> <database name>
  
pg_dump.exe -h localhost --username=operator --format plain --schema-only --schema=castpubs --port=2280 --file=c:\temp\castpubsDDL.sql postgres

Note, don’t forget the parameters:

  • format plain : this will output a plain-text SQL script file
  • schema-only : this will dump only the object definitions (schema), not data

Here is the process using the pgAdmin GUI:

Official documentation

Have a look at the official documentation for more details/parameters and examples: https://www.postgresql.org/docs/9.5/static/app-pgdump.html external link.