GitBucket
4.21.2
Toggle navigation
Snippets
Sign in
Files
Branches
1
Releases
Issues
Pull requests
Labels
Priorities
Milestones
Wiki
Forks
mark.george
/
Wiki
Compare Revisions
View Page
Back to Page History
PostgreSQL.md
## Creating a Database for Java Development For Arch Linux. ``` initdb --encoding=UNICODE --pgdata=pgdata --username postgres ``` The `postgres` user will be added as the administrator with an empty password. `pgdata` is the name of the directory that will hold the database files. The JDBC driver requires some form of Unicode encoding. This will also create the default database named `postgres`. ## Starting PostgreSQL ``` pg_ctl --pgdata=pgdata --log=postgresql.log --options="-k /tmp" start ``` The `-k` is necessary to redirect the lock/socket files somewhere that you have write access to. The default is `/run/postgres` which normal users don't have write permissions for. ## Creating Users/Roles ``` createuser --createdb --interactive --host="localhost" --username postgres --echo ``` The interactive mode will prompt for remaining details (such as role name, if user should be superuser, or be allowed to create new roles). ## Creating New Databases The default `postgres` database is created by `initdb` when the cluster is first created. To create additional databases: Via SQL: ``` CREATE DATABASE newdb OWNER owner; ``` From command line: ``` createdb newdb --username postgres --owner owner --host="localhost" --echo ``` The `-h` points to the directory that contains the lock/socket files used when starting the server. ## Status/Stopping PostgreSQL ``` pg_ctl -D pgdata --options "-k /tmp" status pg_ctl -D pgdata --options "-k /tmp" stop ``` ## JDBC URL Long form: ``` jdbc:postgresql://localhost:5432/postgres ``` `postgres` is the database name. Short form: ``` jdbc:postgresql: ``` `localhost` and `5432` are defaults. By default it will try to connect to a database that matches the username. ## disableColumnSanitiser It can be added to the connection string as a query parameter: ``` jdbc:postgresql://localhost:5432/postgres?disableColumnSanitiser=true ``` The `disableColumnSanitiser` property looks like it might be a solution to the annoying "lowercase column names" issue that makes it hard to switch between PostgresQL and other databases (pretty much all of which return uppercase column names). From the docs: > Setting this to true disables column name sanitiser. The sanitiser folds columns in the resultset to lowercase. The default is to sanitise the columns (off). Need to look into this more - so far not so useful - it might only suppress the lowercasing of quoted column names.
## Creating a Database for Java Development For Arch Linux. ``` initdb --encoding=UNICODE --pgdata=pgdata --username postgres ``` The `postgres` user will be added as the administrator with an empty password. `pgdata` is the name of the directory that will hold the database files. The JDBC driver requires some form of Unicode encoding. This will also create the default database named `postgres`. ## Starting PostgreSQL ``` pg_ctl -D pgdata -l postgresql.log --options="-k /tmp" start ``` The `-k` is necessary to redirect the lock/socket files somewhere that you have write access to. The default is `/run/postgres` which normal users don't have write permissions for. ## Creating New Databases The default `postgres` database is created by `initdb` when the cluster is first created. To create additional databases: Via SQL: ``` CREATE DATABASE newdb OWNER owner; ``` From command line: ``` createdb newdb --username postgres --owner owner -h /tmp ``` The `-h` points to the directory that contains the lock/socket files used when starting the server. ## Status/Stopping PostgreSQL ``` pg_ctl -D pgdata --options "-k /tmp" status pg_ctl -D pgdata --options "-k /tmp" stop ``` ## JDBC URL Long form: ``` jdbc:postgresql://localhost:5432/postgres ``` `postgres` is the database name. Short form: ``` jdbc:postgresql: ``` `localhost` and `5432` are defaults. By default it will try to connect to a database that matches the username. ## disableColumnSanitiser It can be added to the connection string as a query parameter: ``` jdbc:postgresql://localhost:5432/postgres?disableColumnSanitiser=true ``` The `disableColumnSanitiser` property looks like it might be a solution to the annoying "lowercase column names" issue that makes it hard to switch between PostgresQL and other databases (pretty much all of which return uppercase column names). From the docs: > Setting this to true disables column name sanitiser. The sanitiser folds columns in the resultset to lowercase. The default is to sanitise the columns (off). Need to look into this more - so far not so useful - it might only suppress the lowercasing of quoted column names.
GitBucket
4.21.2