Question 185024 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to not need to rewrite SET search_path = ag_catalog every time

Every time I run postgres instance, I need to manually set the search_path to be to ag_catalog by SET search_path=ag_catalog; and then load the age, is there a way makes me don’t need to every time to do that?

Tags:

6

Answers


  1. 0

    The default schema/search_path is ‘public’. In order to change the default search_path to ‘ag_catalog’ instead of public, you need to make a change to the postgresql.conf file inside your db cluster folder. You need to uncomment and change the search_path as

    search_path = 'ag_catalog, "$user", public' # schema names
    Login or Signup to reply.
  2. 0

    You have to change it every time unless you change the default configuration in your postgresql.conf file.

    To edit postgresql.conf:

    1. Navigate to your postgresql directory then navigate to data folder.
    2. Open postgresql.conf file.
    3. Search for search_path attribute then uncomment it and add ag_catalog.
    search_path = '"$user", public,ag_catalog'  # schema names
    Login or Signup to reply.
  3. 0

    If you are using age in only one database in the cluster you can set the search_path for just that database and not change the global setting.

    From here Alter Database

    The remaining forms change the session default for a run-time configuration variable for a PostgreSQL database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value. The database-specific default overrides whatever setting is present in postgresql.conf or has been received from the postgres command line. Only the database owner or a superuser can change the session defaults for a database. Certain variables cannot be set this way, or can only be set by a superuser.

    This means you can do:

    ALTER DATABASE the_db SET search_path = ag_catalog;

    If you need to undo that then:

    ALTER DATABASE the_db SET search_path = default;

    to get back to the postgresql.conf setting.

    Login or Signup to reply.
  4. 0

    When you create a cluster, a folder with the same name as the cluster is created in the PostgreSQL directory. Within this folder, there is a file named postgresql.conf. Open this file using a text editor and locate the line that contains the term "search_path." Remove the ‘#’ symbol at the beginning of the line to uncomment it. Then, modify the line to set the value of search_path as follows:

    search_path = 'ag_catalog, "$user", public'
    Login or Signup to reply.
  5. 0

    Adding to the existing answers, to set the search_path directly from the terminal, use vim to edit the postgresql.conf file. From the installed postgres directory:

    vim {cluster_name}/postgresql.conf

    Once the editor opens up, type /search_path to jump to the line that needs to be modified. Press i to switch to ‘INSERT’ mode, which allows you to insert or delete characters. Once the change has been made, hit esc to get out of ‘INSERT’ mode, then press :wq to write and exit the editor.

    Login or Signup to reply.
  6. 0

    Open postgresql.conf file of the database using any text editor. Make respective changes in the required field. In this case add ag_catalog in the search_path var.

    Save the file and restart server.

    Note: Sometimes this does not work even when the file is changed so you’ll have to find another method in that case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search