0
# PostgreSQL Database Support
1

2
Core PostgreSQL database implementation providing database type registration, connection factory, and database-specific operations for the Flyway migration framework.
3

4
## Capabilities
5

6
### PostgreSQL Database Type
7

8
Registers PostgreSQL database support with Flyway and provides factory methods for creating database instances.
9

10
```java { .api }
11
/**
12
 * PostgreSQL database type implementation
13
 */
14
public class PostgreSQLDatabaseType extends BaseDatabaseType {
15
    /**
16
     * Returns the database name identifier
17
     * @return "PostgreSQL"
18
     */
19
    public String getName();
20
    
21
    /**
22
     * Returns list of supported database engines
23
     * @return List containing "PostgreSQL", "AuroraPostgreSql", "YugabyteDb", "TimescaleDb"
24
     */
25
    public List<String> getSupportedEngines();
26
    
27
    /**
28
     * Returns the null type identifier for this database
29
     * @return Types.NULL
30
     */
31
    public int getNullType();
32
    
33
    /**
34
     * Checks if this database type can handle the given JDBC URL
35
     * @param url JDBC URL to check
36
     * @return true if URL is PostgreSQL-compatible
37
     */
38
    public boolean handlesJDBCUrl(String url);
39
    
40
    /**
41
     * Returns the appropriate JDBC driver class for the URL
42
     * @param url JDBC URL
43
     * @param classLoader Class loader for loading driver
44
     * @return Driver class name (org.postgresql.Driver, com.p6spy.engine.spy.P6SpyDriver, or software.amazon.jdbc.Driver)
45
     */
46
    public String getDriverClass(String url, ClassLoader classLoader);
47
    
48
    /**
49
     * Checks if this database type handles the given database product name and version
50
     * @param databaseProductName Database product name from JDBC metadata
51
     * @param databaseProductVersion Database version
52
     * @param connection JDBC connection
53
     * @return true if product name starts with "PostgreSQL"
54
     */
55
    public boolean handlesDatabaseProductNameAndVersion(String databaseProductName, 
56
                                                       String databaseProductVersion, 
57
                                                       Connection connection);
58
    
59
    /**
60
     * Creates a PostgreSQL database instance
61
     * @param configuration Flyway configuration
62
     * @param jdbcConnectionFactory Connection factory
63
     * @param statementInterceptor Statement interceptor
64
     * @return PostgreSQLDatabase instance
65
     */
66
    public Database createDatabase(Configuration configuration, 
67
                                  JdbcConnectionFactory jdbcConnectionFactory, 
68
                                  StatementInterceptor statementInterceptor);
69
    
70
    /**
71
     * Creates a PostgreSQL parser instance
72
     * @param configuration Flyway configuration
73
     * @param resourceProvider Resource provider for loading SQL files
74
     * @param parsingContext Parsing context
75
     * @return PostgreSQLParser instance
76
     */
77
    public Parser createParser(Configuration configuration, 
78
                              ResourceProvider resourceProvider, 
79
                              ParsingContext parsingContext);
80
    
81
    /**
82
     * Sets default connection properties for PostgreSQL
83
     * @param url JDBC URL
84
     * @param props Properties to modify
85
     * @param classLoader Class loader
86
     */
87
    public void setDefaultConnectionProps(String url, Properties props, ClassLoader classLoader);
88
    
89
    /**
90
     * Gets external authentication properties (e.g., pgpass file support)
91
     * @param url JDBC URL
92
     * @param username Username
93
     * @return Authentication properties
94
     */
95
    public Properties getExternalAuthProperties(String url, String username);
96
}
97
```
98

99
**Usage Examples:**
100

101
```java
102
import org.flywaydb.database.postgresql.PostgreSQLDatabaseType;
103
import org.flywaydb.core.api.configuration.Configuration;
104

105
// The database type is automatically registered via SPI
106
// Check if a URL is handled by PostgreSQL type
107
PostgreSQLDatabaseType dbType = new PostgreSQLDatabaseType();
108
boolean canHandle = dbType.handlesJDBCUrl("jdbc:postgresql://localhost:5432/mydb");
109

110
// Get the appropriate driver class
111
String driverClass = dbType.getDriverClass("jdbc:postgresql://localhost:5432/mydb", 
112
                                          getClass().getClassLoader());
113
```
114

115
### PostgreSQL Database
116

117
Main database implementation providing PostgreSQL-specific operations and migration table management.
118

119
```java { .api }
120
/**
121
 * PostgreSQL database implementation
122
 */
123
public class PostgreSQLDatabase extends Database<PostgreSQLConnection> {
124
    /**
125
     * Creates a new PostgreSQL database instance
126
     * @param configuration Flyway configuration
127
     * @param jdbcConnectionFactory Connection factory
128
     * @param statementInterceptor Statement interceptor
129
     */
130
    public PostgreSQLDatabase(Configuration configuration, 
131
                             JdbcConnectionFactory jdbcConnectionFactory, 
132
                             StatementInterceptor statementInterceptor);
133
    
134
    /**
135
     * Creates a PostgreSQL connection wrapper
136
     * @param connection Raw JDBC connection
137
     * @return PostgreSQLConnection instance
138
     */
139
    protected PostgreSQLConnection doGetConnection(Connection connection);
140
    
141
    /**
142
     * Ensures the PostgreSQL version is supported by Flyway
143
     * @param configuration Flyway configuration
144
     * @throws FlywayException if version is not supported
145
     */
146
    public void ensureSupported(Configuration configuration);
147
    
148
    /**
149
     * Generates the SQL script for creating the Flyway schema history table
150
     * @param table The table to create
151
     * @param baseline Whether to include baseline entry
152
     * @return SQL script for table creation
153
     */
154
    public String getRawCreateScript(Table table, boolean baseline);
155
    
156
    /**
157
     * Gets the current database user
158
     * @return Current user name
159
     * @throws SQLException if user cannot be determined
160
     */
161
    protected String doGetCurrentUser() throws SQLException;
162
    
163
    /**
164
     * Indicates whether PostgreSQL supports DDL transactions
165
     * @return true (PostgreSQL supports DDL in transactions)
166
     */
167
    public boolean supportsDdlTransactions();
168
    
169
    /**
170
     * Returns the PostgreSQL boolean true literal
171
     * @return "TRUE"
172
     */
173
    public String getBooleanTrue();
174
    
175
    /**
176
     * Returns the PostgreSQL boolean false literal
177
     * @return "FALSE"
178
     */
179
    public String getBooleanFalse();
180
    
181
    /**
182
     * Quotes a database identifier for PostgreSQL
183
     * @param identifier The identifier to quote
184
     * @return Quoted identifier with double quotes
185
     */
186
    public String doQuote(String identifier);
187
    
188
    /**
189
     * Returns the escaped quote character for PostgreSQL
190
     * @return "\"\""
191
     */
192
    public String getEscapedQuote();
193
    
194
    /**
195
     * Indicates whether catalog equals schema in PostgreSQL
196
     * @return false
197
     */
198
    public boolean catalogIsSchema();
199
    
200
    /**
201
     * Determines whether to use a single connection based on configuration
202
     * @return true if transactional lock is disabled, false otherwise
203
     */
204
    public boolean useSingleConnection();
205
    
206
    /**
207
     * Gets the SELECT statement for querying the schema history table
208
     * Includes special comment for pgpool load balancing
209
     * @param table The schema history table
210
     * @return SELECT statement with load balancing hint
211
     */
212
    public String getSelectStatement(Table table);
213
    
214
    /**
215
     * Detects the database hosting environment
216
     * @return "AWS RDS" if running on AWS RDS, otherwise default hosting
217
     */
218
    public String getDatabaseHosting();
219
}
220
```
221

222
**Usage Examples:**
223

224
```java
225
import org.flywaydb.database.postgresql.PostgreSQLDatabase;
226
import org.flywaydb.core.api.configuration.ClassicConfiguration;
227

228
// Typically created via PostgreSQLDatabaseType.createDatabase()
229
Configuration config = new ClassicConfiguration();
230
PostgreSQLDatabase database = new PostgreSQLDatabase(config, connectionFactory, interceptor);
231

232
// Check DDL transaction support
233
boolean supportsDdl = database.supportsDdlTransactions(); // returns true
234

235
// Quote an identifier
236
String quoted = database.doQuote("my_table"); // returns "\"my_table\""
237

238
// Generate schema history table creation script
239
Table historyTable = database.getTable("flyway_schema_history");
240
String createScript = database.getRawCreateScript(historyTable, false);
241
```
242

243
## Database Version Support
244

245
- **Minimum Version**: PostgreSQL 9.0
246
- **Premium Features**: Available for PostgreSQL 10+ (requires Flyway premium edition)
247
- **Latest Supported**: PostgreSQL 17 (with upgrade recommendations)
248

249
## Supported JDBC URLs
250

251
- `jdbc:postgresql://host:port/database`
252
- `jdbc:p6spy:postgresql://host:port/database` (P6Spy proxy)
253
- AWS wrapper URLs for RDS support
254

255
## AWS RDS Integration
256

257
The package automatically detects AWS RDS environments and provides:
258

259
- RDS-specific connection handling
260
- Hosting environment detection
261
- Optimized configuration for cloud deployments