PostgreSQL – Equivalent of mysqld –bootstrap or mysqld –init-file

postgresql

I'm trying to automate initializing a postgresql data directory, with a user and a default database pre-created. I know I can do this by running postgresql backgrounded and using createdb and createuser commands, but I really want to avoid that as this is for a docker container and I just want postgres to be running in the foreground once done.

In MySQL I use --init-file /path/to/bootstrap.sql, which runs the statements in the file before the server accepts connections. You can also use --bootstrap, which temporarily spins up mysqld and runs statements from STDIN.

I can't find an equally straightforward way to do this in Postgres. I can see a --boot option to postgres, but when I try and use it I just get "invalid command line arguments", so I must be doing something wrong.

echo "CREATE USER bob WITH PASSWORD 'secure';" | postgres --boot postgres -D data
postgres: invalid command-line arguments

Best Answer

You probably want the single user mode: --single, as described in the documentation:

When invoked in single-user mode from the shell, the user can enter queries and the results will be printed to the screen, but in a form that is more useful for developers than end users. In the single-user mode, the session user will be set to the user with ID 1, and implicit superuser powers are granted to this user. This user does not actually have to exist, so the single-user mode can be used to manually recover from certain kinds of accidental damage to the system catalogs

Note that initdb has be run to initialize an empty data directory before creating databases or users. That would be the equivalent of mysql_install_db if you prefer to look at this from the MySQL analogy angle.

Related Solutions

Postgresql – Oracle Equivalent of PostgreSQL CLUSTER

Similar concept in Oracle is called Index-Organized Tables (IOT).

Difference with PostgreSQL CLUSTER is that in PostgreSQL CLUSTER command reorganizes table once and later table still grows as it wants to. In Oracle IOT keeps its structure as ordered by index.

Unlike Phil I'm seeing IOTs now and then. The most often they are used when you need to retrieve many (think hundreds) rows by index.

PostgreSQL – Equivalent of MySQL Query Variables

This is easy to do inside a PL/pgSQL function (or a DO block):

create function myfunc() returns void language plpgsql as $$
  declare
    aintconst constant int = -333;
    arealconst constant real = -9.99;
    pfid int;
    clientid int;
  begin

    select id from platform where bios like '%INTEL%' into pfid;

    select id from client where platformID = pfid into clientid;

  end $$;

You can also use GUC variables:

--set a session variable
set mycustom.var = 'value';

--use it
select * from mytable where some_column = current_setting('mycustom.var');

Or you can use a CTE with a join:

with myvars as (
  select
    -333::int as aint,
    -9.99::real as areal
)

select 
  a.*
from mytable a
join myvars on true
where
  a.thing = aint

Best Answer

Related Solutions

Postgresql – Oracle Equivalent of PostgreSQL CLUSTER

PostgreSQL – Equivalent of MySQL Query Variables

Related Question