PostgreSQL

https://wiki.postgresql.org/wiki/
Admin
PostgreSQL is a powerful, open-source object-relational database system known for its reliability,...
Tokens:2,256,949
Snippets:11,609
Trust Score:10
Update:11 months ago
Show doc for...
Tokens:
Raw
### Quickstart: Install PostgreSQL APT Repository

Source: https://wiki.postgresql.org/wiki/Apt

This snippet provides a quick way to install the PostgreSQL APT repository and common packages. It first installs essential packages and then runs a script to configure the repository.

```bash
sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
```

--------------------------------

### Specifying Installation Schema with psql (SQL)

Source: https://wiki.postgresql.org/wiki/PGXN

This example demonstrates how to use the `psql` command-line utility to specify a target schema for installing an extension. This allows for more organized management of extension scripts.

```sql
psql --set INSTALLSCHEMA=extensions extesion.sql
```

--------------------------------

### Start PostgreSQL Service - Linux Shell

Source: https://wiki.postgresql.org/wiki/Converting_your_data_from_MS_SQL_Server_7_to_PostgreSQL_7

This command initiates the PostgreSQL database service on a RedHat Linux system. It requires root privileges to execute. The command starts the PostgreSQL server, making the database accessible. Ensure PostgreSQL is installed before running this.

```bash
service PostgreSQL start
```

--------------------------------

### Prevent Automatic Service Startup on Debian/Ubuntu

Source: https://wiki.postgresql.org/wiki/Detailed_installation_guides

This script prevents PostgreSQL and Slony services from starting automatically during package upgrades on Debian-based systems. It involves creating a policy-rc.d script to control service behavior.

```shell
echo 'if $1 in postgresql-9.3|slony1-2 then exit 101 else exit 0' > /usr/sbin/policy-rc.d
chmod +x /usr/sbin/policy-rc.d
```

--------------------------------

### Start Postgres-XL Components with pg_ctl

Source: https://wiki.postgresql.org/wiki/Scaleout_Design

Demonstrates starting Postgres-XL coordinator and datanode components using the 'pg_ctl start' command with specific zone arguments.

```bash
pg_ctl start -Z coordinator -D /usr/local/pgsql/coorddata
pg_ctl start -Z datanode -D /usr/local/pgsql/data
```

--------------------------------

### Configure and Install PostgreSQL

Source: https://wiki.postgresql.org/wiki/DBT-2

This snippet demonstrates the basic steps for configuring and installing PostgreSQL from source. It includes setting a custom installation directory, building the software, and installing it. It also mentions checking configuration options and PostgreSQL documentation.

```bash
./configure --prefix=<installation dir>
make
make install
```

--------------------------------

### Start and manage PostgreSQL services with Homebrew

Source: https://wiki.postgresql.org/wiki/Homebrew

These commands are used to start the PostgreSQL server installed via Homebrew. 'brew services start postgresql' starts the server and ensures it restarts at boot time, while 'brew services run postgresql' starts it without automatic restart. The pg_ctl command is also mentioned as an alternative. These commands assume PostgreSQL has already been installed using Homebrew.

```bash
$ brew services start postgresql # or "brew services run postgresql" to have it not restart at boot time
# or the pg_ctl command it mentions
```

--------------------------------

### 026_overwrite_contrecord.pl Failure in Streaming Replication Setup

Source: https://wiki.postgresql.org/wiki/Known_Buildfarm_Test_Failures

This snippet details an error during the setup of streaming replication for the 026_overwrite_contrecord.pl test. The 'pg_ctl start' command failed to start the server on the standby node, indicated by an exit status of 255 or signal 127. This suggests a potential issue with recovery or server initialization.

```text
325.72s   (exit status 255 or signal 127 SIGinvalid)

--- /home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/recovery/026_overwrite_contrecord/data/t_026_overwrite_contrecord_standby_data/pgdata -l 
/home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/recovery/026_overwrite_contrecord/log/026_overwrite_contrecord_standby.log -o --cluster-name=standby start
waiting for server to start........ stopped waiting
pg_ctl: could not start server
Examine the log output.
# pg_ctl start failed; see logfile for details: /home/bf/bf-build/skink-master/HEAD/pgsql.build/testrun/recovery/026_overwrite_contrecord/log/026_overwrite_contrecord_standby.log

```

--------------------------------

### Build and Install PostgreSQL and SE-PostgreSQL

Source: https://wiki.postgresql.org/wiki/SEPostgreSQL_Draft

Standard build and installation commands for PostgreSQL after enabling SE-PostgreSQL support. Includes 'make', 'make install', creating a data directory, and setting ownership.

```bash
make
su
# make install
# mkdir -p /opt/sepgsql
# chown sepgsql:users -R /opt/sepgsql
```

--------------------------------

### Start Oracle Database Instance

Source: https://wiki.postgresql.org/wiki/Scaleout_Design

Sequential steps to start an Oracle database instance, beginning with the shard catalog and local listener, followed by shard directors, shard databases, global services, and finally connection pools and clients.

```sql
-- 1. Start the shard catalog database and local listener.
-- 2. Start the shard directors (GMSs).
-- 3. Start up the shard databases and local listeners.
-- 4. Start the global services.
-- 5. Start the connection pools and clients.
```

--------------------------------

### Checkout, Build, and Install PostgreSQL Project

Source: https://wiki.postgresql.org/wiki/Working_with_CVS

This sequence of commands outlines the process of checking out a new project, compiling it, installing it, and setting up environment variables for development. It includes creating a project directory, checking out the 'pgsql' module, renaming it for clarity, configuring with specific build flags, compiling, and installing.

```shell
$ cd ~ 
~/$ mkdir project
~/$ cd project
~/project$ cvs co pgsql  # could do "cvs co -d pgsql.project pgsql" instead of these two lines
~/project$ mv pgsql pgsql.project   
~/project$ cd pgsql.project
~/project/pgsql.project$ cvs update -dP
~/project/pgsql.project$ ./configure --prefix=$HOME/project --enable-depend --enable-cassert --enable-debug
~/project/pgsql.project$ make
~/project/pgsql.project$ make install
~/project/pgsql.project$ export PGDATA=<your database directory>
~/project/pgsql.project$ export PATH=$HOME/project/bin:$PATH
~/project/pgsql.project$ initdb
~/project/pgsql.project$ pg_ctl start 
~/project/pgsql.project$ psql
```

--------------------------------

### Install PostgreSQL Package

Source: https://wiki.postgresql.org/wiki/Apt

After configuring the APT repository, this snippet demonstrates how to update the package lists and install a specific PostgreSQL version. It's a final step in the setup process.

```bash
sudo apt update
sudo apt install postgresql-17
```

--------------------------------

### Basic PGXN Client Build and Install Commands (Shell)

Source: https://wiki.postgresql.org/wiki/PGXN

These commands represent the initial implementation steps for the PGXN client, assuming extensions can be built using PGXS. They cover the build, install, and test phases of an extension.

```shell
make USE_PGXS=1
make USE_PGXS=1 install
make USE_PGXS=1 installcheck
```

--------------------------------

### Install Ticker and Produce Ticks (Shell)

Source: https://wiki.postgresql.org/wiki/Londiste_Tutorial_%28Skytools_2%29

Shell commands to install the PGQ support code into the database and start the Londiste ticker daemon. The 'pgqadm.py' script is used for these operations.

```shell
pgqadm.py ticker.ini install
pgqadm.py ticker.ini ticker -d
```

--------------------------------

### Run PostgreSQL Installer on Windows (Interactive)

Source: https://wiki.postgresql.org/wiki/Russian/PostgreSQL-One-click-Installer-Guide

This command executes the PostgreSQL installer on Windows. It is a standard executable file that can be run directly from the command prompt.

```cmd
C:\> postgresql-8.4.0-1-windows.exe

```

--------------------------------

### Go Example with postgres_fdw and DTM

Source: https://wiki.postgresql.org/wiki/DTM

Demonstrates transaction execution in Go when using postgres_fdw with DTM. This example shows the pattern of initiating a DTM transaction with `dtm_begin_transaction`, starting a local transaction, performing updates, and then committing.

```go
exec(con, "select dtm_begin_transaction()")
exec(con, "begin transaction")
exec(con, "update t set v = v + $1 where u=$2", amount, account1)
exec(con, "update t set v = v - $1 where u=$2", amount, account2)
exec(con, “commit”)
```

--------------------------------

### Control PostgreSQL Service (Systemd)

Source: https://wiki.postgresql.org/wiki/RPM_Installation

Commands to manage the PostgreSQL database service using systemd. This includes enabling the service to start on boot, starting the service, stopping it, restarting it, and reloading its configuration. Replace '12' with the major version of PostgreSQL.

```shell
systemctl enable postgresql-12.service
systemctl start postgresql-12.service
```

--------------------------------

### Connect to PostgreSQL using psql (Windows)

Source: https://wiki.postgresql.org/wiki/First_steps

Connects to the PostgreSQL database using the psql command-line client on Windows. The user executing the command can be different from the database user.

```bash
c:\path\to\psql.exe -U postgres
```

--------------------------------

### Start PostgreSQL Backup for Replication

Source: https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

Initiates a backup on the PostgreSQL primary server using the 'pg_start_backup' function. This function prepares the database for file copying by creating a backup label and ensuring data consistency during the subsequent file transfer.

```sql
psql -U postgres
# select pg_start_backup('clone',true);

```

--------------------------------

### PgAdmin Coding Style Example

Source: https://wiki.postgresql.org/wiki/PgAdmin_Internals

Demonstrates the application of coding style conventions to a C++ function. It highlights improvements in readability through consistent indentation, brace placement, and spacing. This example serves as a guide for adhering to the project's coding standards.

```cpp
// Check if an item exists in the array.
bool gqbArrayCollection::existsObject(gqbObject *item)
{
	gqbObject *found=NULL;
	int size=gqbArray.GetCount();

	for (int i=0; i<size; i++)
	{
		if (gqbArray.Item(i)==item)
		{
	 	 	 found=gqbArray.Item(i);
	 	 	 break;
		}
	}

	 if (found)
	 	 return true;
	 else
	 	 return false;
}

```

--------------------------------

### DB2 ALTER TABLE to Attach Partition Example

Source: https://wiki.postgresql.org/wiki/Table_partitioning

Shows how to add a new partition to an existing table in DB2 using the ALTER TABLE statement. This example demonstrates attaching a new partition named 'q1_07' with specific start and end dates, sourcing data from another table.

```sql
ALTER TABLE orders
   ATTACH PARTITION q1_07
   STARTING '01/01/2007'
   ENDING   '03/31/2007'
   FROM TABLE neworders
```

--------------------------------

### Meson Build Setup and Configuration Commands

Source: https://wiki.postgresql.org/wiki/Meson

This snippet covers commands for initializing, configuring, and managing build trees with Meson. It contrasts the older Autoconf commands with their Meson equivalents, showing how to set build options, specify compilers, and manage compiler flags.

```bash
# Setup build tree
meson setup [_options_] [_builddir_] _sourcedir_

# Setup build tree for Visual Studio
meson setup --backend vs [_options_] [_builddir_] _sourcedir_

# Show configure options
meson configure

# Set configure options (e.g., enable cassert)
meson setup -Dcassert=true
meson configure -Dcassert=true

# Specify compiler
CC=_compiler_ meson setup

# Set CFLAGS
meson setup -Dc_args=_options_
meson configure -Dc_args=_options_

```

--------------------------------

### Configure, Build, and Install Skytools 2.x

Source: https://wiki.postgresql.org/wiki/Londiste_Tutorial

Steps to configure, build, and install the Skytools 2.x application. This process involves using the configure script with specific options for prefix and pgconfig, followed by make and make install commands. It also includes building and installing the Python components using setup.py.

```shell
> tar zxf skytools-2.x.x.tar.gz
> cd skytools-2.x.x
```

```shell
> ./configure --prefix=/opt/skytools-2.x.x --with-pgconfig=/opt/postgres-9.0.1/bin/pg_config
```

```shell
> make
> sudo make install
```

```shell
> python setup.py build
> sudo python setup.py install
```

--------------------------------

### Start and Stop PostgreSQL Server (Windows Batch)

Source: https://wiki.postgresql.org/wiki/Russian/Starting-PostgreSQL-in-windows-without-install

This script starts a PostgreSQL server and allows it to be shut down by pressing Enter. It sets environment variables for PostgreSQL and uses `pg_ctl` to manage the server. It's designed to be placed in the root of the unzipped PostgreSQL folder. For first-time use, the `initdb` command needs to be uncommented to initialize the database.

```batch
@ECHO ON
REM The script sets environment variables helpful for PostgreSQL
@SET PATH="%~dp0\bin";%PATH%
@SET PGDATA=%~dp0\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5439
@SET PGLOCALEDIR=%~dp0\share\locale
REM "%~dp0\bin\initdb" -U postgres -A trust
"%~dp0\bin\pg_ctl" -D "%~dp0/data" -l logfile start
ECHO "Click enter to stop"
pause
"%~dp0\bin\pg_ctl" -D "%~dp0/data" stop
```

--------------------------------

### Create Base Software Directory - Bash

Source: https://wiki.postgresql.org/wiki/Simple_Configuration_Recommendation

This snippet demonstrates how to create a base directory for PostgreSQL software installation on a Linux system. It assumes the user has appropriate permissions.

```bash
mkdir /opt/postgres
```
Tokens:
Raw