Practical PostgreSQL Operations and Solutions
Tech
1
tk.mybatis Pagination Control
// Set maximum results for query
// Append SQL clause directly as shown below
// Note SQL keyword order for similar requirements
Example queryExample = new Example(Entity.class);
queryExample.setOrderByClause("driver_code ASC LIMIT " + maxResults);
PostgreSQL Backup Operations
Database Backup
# Grant permissions to target directory
chown postgres:postgres /target
# Switch to postgres user
su - postgres
# Execute backup
cd /opt/fsepv13server64/bin
# Replace 'database' with actual database name
# -s flag backs up schema only (no data)
./pg_dump -Fp -s database > /target/schema_backup.sql
Configuration Reload
# Reload PostgreSQL configuration
pg_ctl reload -D /inst/data # Replace with actual data directory
Data Import/Export with COPY
Binary Format Export
-- Export result set as binary data
-- Format changed in PostgreSQL 7.4
\copy (SELECT template FROM table WHERE ...) TO '/target/output.data' WITH (FORMAT binary)
Command-line Binary Operatoins
# Export via psql
psql -h host -p port -d dbname -X -c "COPY (SELECT col FROM table) TO STDOUT WITH BINARY" > data.bin
# Import via SQL
COPY target_table(column_list) FROM 'data.bin' WITH (FORMAT binary);
Troubleshootign
JIT Compilation Error
Error Message:
ERROR: could not load library "/opt/fsepv13server64/lib/llvmjit.so": libLLVM-10.so: cannot open shared object file
Cause:
JIT enabled but required library missing
Solution:
Disable JIT in postgresql.conf
jit = off
