{"id":363,"date":"2025-06-07T09:47:09","date_gmt":"2025-06-07T12:47:09","guid":{"rendered":"https:\/\/lemosdba.com.br\/?p=363"},"modified":"2026-02-05T13:55:29","modified_gmt":"2026-02-05T16:55:29","slug":"movimentacao-de-objetos-tabela-e-indice-no-postgresql","status":"publish","type":"post","link":"https:\/\/lemosdba.com.br\/en\/movimentacao-de-objetos-tabela-e-indice-no-postgresql\/","title":{"rendered":"Moving objects (table and index) in PostgreSQL"},"content":{"rendered":"<h1 class=\"wp-block-heading\">Introduction<\/h1>\n\n\n\n<p class=\"has-medium-font-size\">Organizing objects in the database is relatively simple to manage and is fundamental to application architecture. In certain cases, it can also be used to improve read and write performance. The definitions of owner, schema, and tablespace for table and index objects will be addressed, as well as how to modify them.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Configura\u00e7\u00f5es iniciais &#8211; setup<\/h1>\n\n\n\n<p class=\"has-medium-font-size\">The initial configurations must be applied so that the commands used in the following sections can be executed successfully.<br><br>The initial configurations were divided into:<br><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"has-medium-font-size\">Database: a test database in a PostgreSQL cluster in order to centralize and configure objects.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">Tablespaces: creation of tablespaces in the database based on directories configured in the filesystem of the database server. The tablespaces will be used to physically allocate objects.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">Schemas: configured to provide logical organization of objects in the database.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">Roles\/Users: created to demonstrate access control and ownership of specific objects.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">Privileges: allow user\/role actions to be performed on specific database resources.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">Objects: created to demonstrate their definitions and configuration processes.<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted has-medium-font-size\"><strong>Note:<\/strong> If you wish to carry out these configurations, apply them in a test-friendly environment in order to mitigate errors in critical environments.<\/pre>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>mkdir \/data\/tbs_app_test_tbl\n\nmkdir \/data\/tbs_app_test_idx\n\nchown postgres:postgres \/data\/tbs_app_test_tbl\n\nchown postgres:postgres \/data\/tbs_app_test_idx\n\npsql\n\ncreate database db_test;\n\nrevoke connect on database db_test from public;\n\n\\c db_test\n\ncreate tablespace tbs_app_test_tbl location '\/data\/tbs_app_test_tbl';\n\ncreate tablespace tbs_app_test_idx location '\/data\/tbs_app_test_idx';\n\ncreate user testuser with encrypted password 'strongpassword01';\n\ncreate role role_app_query_test;\n\ncreate user user_aux_test with encrypted password 'strongpassword02' in role role_app_query_test;\n\ngrant create on tablespace tbs_app_test_tbl to user_app_test;\n\ngrant create on tablespace tbs_app_test_idx to user_app_test;\n\ngrant connect on database db_test to user_app_test;\n\ngrant connect on database db_test to user_aux_test;\n\ncreate schema schema_user_app_test_default;\n\ncreate schema schema_user_app_test_01;\n\ncreate schema schema_user_app_test_02;\n\ncreate schema schema_user_aux_test_default;\n\ngrant all privileges on schema schema_user_app_test_01 to user_app_test;\n\ngrant all privileges on schema schema_user_app_test_02 to user_app_test;\n\ngrant all privileges on schema schema_user_aux_test_default to user_aux_test;\n\nalter user user_app_test set search_path to schema_user_app_test_default;\n\nalter user user_aux_test set search_path to schema_user_aux_test_default;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #88C0D0\">mkdir<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_tbl<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">mkdir<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_idx<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">chown<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">postgres:postgres<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_tbl<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">chown<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">postgres:postgres<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_idx<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">psql<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">database<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">db_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">revoke<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">connect<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">database<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">db_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">from<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">public<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">\\c<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">db_test<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tablespace<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tbs_app_test_tbl<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">location<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_tbl<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tablespace<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tbs_app_test_idx<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">location<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">\/data\/tbs_app_test_idx<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">testuser<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">with<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">encrypted<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">password<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">strongpassword01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">role<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">role_app_query_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_aux_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">with<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">encrypted<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">password<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">strongpassword02<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">in<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">role<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">role_app_query_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tablespace<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tbs_app_test_tbl<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tablespace<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">tbs_app_test_idx<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">connect<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">database<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">db_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">connect<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">database<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">db_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_aux_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_default<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_01<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_02<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_aux_test_default<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">all<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">privileges<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_01<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">all<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">privileges<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_02<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">all<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">privileges<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">on<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_aux_test_default<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_aux_test<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">set<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">search_path<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_app_test_default<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #88C0D0\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">user_aux_test<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">set<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">search_path<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">to<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #A3BE8C\">schema_user_aux_test_default<\/span><span style=\"color: #81A1C1\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<div class=\"wp-block-media-text is-stacked-on-mobile\" style=\"grid-template-columns:69% auto\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"533\" height=\"119\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/01-2.png\" alt=\"\" class=\"wp-image-409 size-full\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/01-2.png 533w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/01-2-300x67.png 300w\" sizes=\"auto, (max-width: 533px) 100vw, 533px\" \/><\/figure><div class=\"wp-block-media-text__content\">\n<p>Configuration of tablespace directories on the database server.<\/p>\n<\/div><\/div>\n\n\n\n<p><\/p>\n\n\n\n<div class=\"wp-block-media-text is-stacked-on-mobile\" style=\"grid-template-columns:69% auto\"><figure class=\"wp-block-media-text__media\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"963\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/02-1024x963.png\" alt=\"\" class=\"wp-image-397 size-full\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/02-1024x963.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/02-300x282.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/02-768x722.png 768w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/02.png 1074w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><div class=\"wp-block-media-text__content\">\n<p>Configuration of the database, tablespaces, schemas, users\/roles, and privileges.<\/p>\n<\/div><\/div>\n\n\n\n<p><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Examples of object creation and movement<\/h1>\n\n\n\n<p class=\"has-medium-font-size\">Three general cases will be covered:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"has-medium-font-size\">01 &#8211; Cria\u00e7\u00e3o de objetos em owner, schema e tablespace diferentes dos necess\u00e1rios, utilizando usu\u00e1rio diferente do owner dos objetos.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">02 &#8211; Cria\u00e7\u00e3o de objetos especificando owner, schema e tablespace necess\u00e1rios, utilizando usu\u00e1rio diferente do owner dos objetos.<\/li>\n\n\n\n<li class=\"has-medium-font-size\">03 &#8211; Cria\u00e7\u00e3o de objetos especificando schema e tablespace necess\u00e1rios, utilizando o owner dos objetos.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Creating objects with an owner, schema, and tablespace different from the required ones, using a user other than the object owner.<\/h2>\n\n\n\n<p class=\"has-medium-font-size\">In the database (db_test), table and index objects were created to demonstrate their properties regarding owner, schema, and tablespace, that is, who owns the object and what are its logical and physical organizations, respectively.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>create table table_proc_01 (id serial,\n                            name varchar(30),\n                            data text);\n\ncreate index idx_table_proc_01_col_id on table_proc_01(id);\n\ngrant select on table_proc_01 to role_app_query_test;\n\nselect * \nfrom information_schema.table_privileges\nwhere table_name = 'table_proc_01';\n\nselect current_database()\n      ,pg_get_userbyid(pc.relowner) as table_owner\n\t,psat.relname as table_name\n\t,psat.schemaname as table_schema\n\t,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n\t                                else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace\nfrom pg_class pc\njoin pg_stat_all_tables psat on psat.relid = pc.oid\nwhere psat.schemaname not in ('pg_toast','pg_catalog','information_schema')\norder by table_owner,\n\t  table_name;\n\nselect current_database(), session_user, current_user, current_schema;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">table_proc_01<\/span><span style=\"color: #D8DEE9FF\"> (id <\/span><span style=\"color: #81A1C1\">serial<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                            <\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">30<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                            <\/span><span style=\"color: #81A1C1\">data<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">text<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">index<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">idx_table_proc_01_col_id<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> table_proc_01(id);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">grant<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> table_proc_01 <\/span><span style=\"color: #81A1C1\">to<\/span><span style=\"color: #D8DEE9FF\"> role_app_query_test;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> information_schema.table_privileges<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> table_name <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">table_proc_01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.relname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.schemaname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_schema<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t                                <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace)::<\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_all_tables psat <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> psat.relid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> psat.schemaname <\/span><span style=\"color: #81A1C1\">not<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_toast<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_catalog<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">information_schema<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> table_owner,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t  table_name;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">, session_user, current_user, current_schema;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bcbeb&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bcbeb\" class=\"wp-block-image size-full is-resized wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"1887\" height=\"943\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1.png\" alt=\"\" class=\"wp-image-416\" style=\"width:1200px;height:auto\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1.png 1887w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1-300x150.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1-1024x512.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1-768x384.png 768w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03a-1-1536x768.png 1536w\" sizes=\"auto, (max-width: 1887px) 100vw, 1887px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<p class=\"has-medium-font-size\">In the example above, the objects were created using the postgres superuser. It can be observed that the table\u2019s owner (table_owner) was, by default, assumed as the postgres user (current_user), the schema was public (current_schema), and the tablespace was pg_default (table_tablespace). To properly organize the objects in the database, the table\u2019s definitions can be modified.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Changing the table\u2019s owner and schema<\/h3>\n\n\n\n<p class=\"has-medium-font-size\">The table can be assigned a different owner and organized into a different schema, as shown in the example below.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>alter table table_proc_01 owner to user_app_test;\n\nalter table public.table_proc_01 set schema schema_user_app_test_01;\n\nselect * \nfrom information_schema.table_privileges\nwhere table_name = 'table_proc_01';\n\nselect current_database()\n      ,pg_get_userbyid(pc.relowner) as table_owner\n\t,psat.relname as table_name\n\t,psat.schemaname as table_schema\n\t,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n\t                                else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace\nfrom pg_class pc\njoin pg_stat_all_tables psat on psat.relid = pc.oid\nwhere psat.schemaname not in ('pg_toast','pg_catalog','information_schema')\norder by table_owner,\n\t  table_name;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> table_proc_01 <\/span><span style=\"color: #81A1C1\">owner<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">to<\/span><span style=\"color: #D8DEE9FF\"> user_app_test;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> public.table_proc_01 <\/span><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">schema<\/span><span style=\"color: #D8DEE9FF\"> schema_user_app_test_01;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> information_schema.table_privileges<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> table_name <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">table_proc_01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.relname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.schemaname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_schema<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t                                <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace)::<\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_all_tables psat <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> psat.relid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> psat.schemaname <\/span><span style=\"color: #81A1C1\">not<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_toast<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_catalog<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">information_schema<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> table_owner,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t  table_name;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bce75&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bce75\" class=\"wp-block-image aligncenter size-large wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"385\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1-1024x385.png\" alt=\"\" class=\"wp-image-420\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1-1024x385.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1-300x113.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1-768x289.png 768w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1-1536x577.png 1536w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03b-1.png 1886w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<p class=\"has-medium-font-size\">In the example above, the table that had postgres as its owner was transferred to user_app_test, and its schema was changed from public to schema_user_app_test_01. It can also be observed that the privileges were updated, and the grantor, which was previously postgres, became user_app_test.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Changing the table\u2019s tablespace<\/h3>\n\n\n\n<p class=\"has-medium-font-size\">The table can be allocated to a different tablespace. Generally speaking, there is no strict need or significant benefit in creating tablespaces to store objects outside the default tablespace and thus organize them physically; such a need may arise based on performance analyses. Here, the possibility of changing the tablespace will be demonstrated, noting that a lock is applied to the table, causing it to be unavailable for a certain period, which varies depending on the table\u2019s size.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted has-medium-font-size\"><strong>Note:<\/strong> Moving a table between tablespaces generates locks, which can negatively impact its availability. When this needs to be done, attention should be paid to the timing of the operation and the method used for the move, whether it will be performed using PostgreSQL\u2019s native\/basic approach or with an extension to assist in the process.<\/pre>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>do $$\nbegin\n\tfor i in 1..1000000 loop\n\t\tinsert into schema_user_app_test_01.table_proc_01 (name, data)\n\t\tvalues ('name', 'data');\n\tend loop;\nend $$;\n\nalter table schema_user_app_test_01.table_proc_01 set tablespace tbs_app_test_tbl;\n\n----- Em uma segunda sess\u00e3o, paralela a altera\u00e7\u00e3o da tablespace na primeira sess\u00e3o, executar os comandos abaixo.\n\\x on\n\nselect pd.datname\n      ,pg_get_userbyid(pc.relowner) owner\n      ,pc.relname\n      ,psa.usename\n      ,psa.pid\n      ,psa.query\n      ,psa.query_start\n      ,round(extract(epoch from (now() - psa.state_change)),2) as elapsed_time_sec\n      ,psa.state\n      ,case when pl.granted = 'True' then 'helds lock'\n                                     else 'lock awaited' end as granted\n      ,pl.locktype\n      ,mode\nfrom pg_locks pl\njoin pg_class pc on pl.relation = pc.oid\njoin pg_stat_activity psa on pl.pid = psa.pid\njoin pg_database pd on pl.database = pd.oid\nwhere relname = 'table_proc_01';\n----- Em uma segunda sess\u00e3o, paralela a altera\u00e7\u00e3o da tablespace na primeira sess\u00e3o, observar o output dos comandos acima.\n\nselect current_database()\n       ,pg_get_userbyid(pc.relowner) as table_owner\n\t,psat.relname as table_name\n\t,psat.schemaname as table_schema\n\t,case when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n\t                                else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace\nfrom pg_class pc\njoin pg_stat_all_tables psat on psat.relid = pc.oid\nwhere psat.schemaname not in ('pg_toast','pg_catalog','information_schema')\norder by table_owner,\n\t  table_name;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">do $$<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">begin<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t<\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> i <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">..<\/span><span style=\"color: #B48EAD\">1000000<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">loop<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t\t<\/span><span style=\"color: #81A1C1\">insert into<\/span><span style=\"color: #D8DEE9FF\"> schema_user_app_test_01.table_proc_01 (<\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #81A1C1\">data<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t\t<\/span><span style=\"color: #81A1C1\">values<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">name<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">data<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t<\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">loop<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> $$;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> schema_user_app_test_01.table_proc_01 <\/span><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> tablespace tbs_app_test_tbl;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">----- Em uma segunda sess\u00e3o, paralela a altera\u00e7\u00e3o da tablespace na primeira sess\u00e3o, executar os comandos abaixo.<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\\x <\/span><span style=\"color: #81A1C1\">on<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pd.datname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pc.relname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.usename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.pid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.query<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.query_start<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">(extract(epoch <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">now<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\"> psa.state_change)),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> elapsed_time_sec<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.state<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pl.granted <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">True<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">helds lock<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                                     <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">lock awaited<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> granted<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pl.locktype<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,mode<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_locks pl<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.relation <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_activity psa <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.pid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> psa.pid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.database <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pd.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> relname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">table_proc_01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">----- Em uma segunda sess\u00e3o, paralela a altera\u00e7\u00e3o da tablespace na primeira sess\u00e3o, observar o output dos comandos acima.<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">       ,pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.relname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,psat.schemaname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_schema<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t                                <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace)::<\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_all_tables psat <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> psat.relid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> psat.schemaname <\/span><span style=\"color: #81A1C1\">not<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_toast<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_catalog<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">information_schema<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> table_owner,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t  table_name;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bd09f&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bd09f\" class=\"wp-block-image aligncenter size-large is-resized wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"334\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c-1024x334.png\" alt=\"\" class=\"wp-image-425\" style=\"width:1200px;height:auto\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c-1024x334.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c-300x98.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c-768x250.png 768w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c-1536x500.png 1536w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03c.png 1676w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<p class=\"has-medium-font-size\">In the example above, before the move, records were added to the test table. During the table\u2019s movement between tablespaces, in a second session connected to the db_test database, the lock (AccessExclusiveLock) generated on the table was checked.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Changing the index\u2019s tablespace<\/h3>\n\n\n\n<p class=\"has-medium-font-size\">The possibility of changing the tablespace will also be demonstrated here, noting that a lock is applied to the index, causing it to be unavailable for a certain period, which varies depending on its size. It is observed that even after changing the tablespace of the table associated with the index, the index remains allocated in its original tablespace.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted has-medium-font-size\"><strong>Note:<\/strong> Moving an index between tablespaces generates locks, which can negatively impact its availability. When this needs to be done, attention should be paid to the timing of the operation and the method used, whether it will be performed using PostgreSQL\u2019s native\/basic approach or with an extension to assist in the process.<\/pre>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>select schemaname\n      ,tablename\n      ,indexname\n      ,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n            else pi.tablespace end as table_tablespace\nfrom pg_indexes pi\nwhere tablename = 'table_proc_01'\norder by schemaname\n        ,tablename\n        ,indexname;\n\nalter index schema_user_app_test_01.idx_table_proc_01_col_id set tablespace tbs_app_test_idx;\n\nselect pd.datname\n      ,pg_get_userbyid(pc.relowner) owner\n      ,pc.relname\n      ,psa.usename\n      ,psa.pid\n      ,psa.query\n      ,psa.query_start\n      ,round(extract(epoch from (now() - psa.state_change)),2) as elapsed_time_sec\n      ,psa.state\n      ,case when pl.granted = 'True' then 'helds lock'\n                                     else 'lock awaited' end as granted\n      ,pl.locktype\n      ,mode\nfrom pg_locks pl\njoin pg_class pc on pl.relation = pc.oid\njoin pg_stat_activity psa on pl.pid = psa.pid\njoin pg_database pd on pl.database = pd.oid\nwhere relname = 'idx_table_proc_01_col_id';\n\nselect schemaname\n      ,tablename\n      ,indexname\n      ,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n            else pi.tablespace end as table_tablespace\nfrom pg_indexes pi\n--where tablename = 'table_proc_01'\norder by schemaname\n        ,tablename\n        ,indexname;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,indexname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">is<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_indexes pi<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> tablename <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">table_proc_01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,indexname;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">alter<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">index<\/span><span style=\"color: #D8DEE9FF\"> schema_user_app_test_01.idx_table_proc_01_col_id <\/span><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> tablespace tbs_app_test_idx;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pd.datname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">owner<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pc.relname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.usename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.pid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.query<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.query_start<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #88C0D0\">round<\/span><span style=\"color: #D8DEE9FF\">(extract(epoch <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">now<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\"> psa.state_change)),<\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> elapsed_time_sec<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,psa.state<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pl.granted <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">True<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">helds lock<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                                     <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">lock awaited<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> granted<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,pl.locktype<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,mode<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_locks pl<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.relation <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_activity psa <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.pid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> psa.pid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pl.database <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pd.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> relname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">idx_table_proc_01_col_id<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,indexname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">is<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_indexes pi<\/span><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">--where tablename = &#39;table_proc_01&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,indexname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bd299&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bd299\" class=\"wp-block-image aligncenter size-large wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"394\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d-1024x394.png\" alt=\"\" class=\"wp-image-429\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d-1024x394.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d-300x116.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d-768x296.png 768w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d-1536x592.png 1536w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/03d.png 1607w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<p class=\"has-medium-font-size\">During the movement of the index between tablespaces, in a second session connected to the db_test database, the lock (AccessExclusiveLock) generated on the table was checked.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Creating objects specifying owner, schema, and tablespace, using a user other than the object owner<\/h2>\n\n\n\n<p class=\"has-medium-font-size\">When connected to the database with a user different from the one who should own the objects, the role can be set (SET ROLE) so that the object is created under the appropriate owner. In this case, the connection is being made using the postgres superuser. In the CREATE command example below, the schema and tablespace are explicitly specified. After the object is created, the role is reset and returns to its default value.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>set role 'user_app_test';\n\nselect current_database(), session_user, current_user, current_schema;\n\ncreate table schema_user_app_test_02.table_proc_02 (id serial,\n                                                    name varchar(30),\n                                                    data text) tablespace tbs_app_test_tbl;\n\nreset role;\n\nselect current_database(), session_user, current_user, current_schema;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">set<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">role<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">user_app_test<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">, session_user, current_user, current_schema;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">schema_user_app_test_02<\/span><span style=\"color: #D8DEE9FF\">.table_proc_02 (id <\/span><span style=\"color: #81A1C1\">serial<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                                                    <\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">30<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                                                    <\/span><span style=\"color: #81A1C1\">data<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">text<\/span><span style=\"color: #D8DEE9FF\">) tablespace tbs_app_test_tbl;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">reset<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">role<\/span><span style=\"color: #D8DEE9FF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">, session_user, current_user, current_schema;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bd480&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bd480\" class=\"wp-block-image size-full is-resized wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"797\" height=\"740\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/04.png\" alt=\"\" class=\"wp-image-439\" style=\"width:1200px;height:auto\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/04.png 797w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/04-300x279.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/04-768x713.png 768w\" sizes=\"auto, (max-width: 797px) 100vw, 797px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Creating objects specifying schema and tablespace, using the object owner<\/h2>\n\n\n\n<p class=\"has-medium-font-size\">In the example below, a connection was made to the db_test database using the user (user_app_test) who will be the owner of the objects. The objects will have the desired owner, and the schema and tablespace are specified in the CREATE commands.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers cbp-highlight-hover\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#d8dee9ff;--cbp-line-number-width:calc(2 * 0.6 * .875rem);--cbp-line-highlight-color:rgba(201, 218, 248, 0.2);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewbox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>psql -h localhost -p 5432 -U user_app_test -d db_test\n\nselect current_database(), session_user, current_user, current_schema;\n\ncreate table schema_user_app_test_01.table_aux_01 (id serial,\n                   name varchar(30),\n                   data text) tablespace tbs_app_test_tbl;\n\ncreate index idx_table_aux_01_col_id on schema_user_app_test_01.table_aux_01(id) tablespace tbs_app_test_idx;\n\ncreate table schema_user_app_test_02.table_aux_02 (id serial,\n                   name varchar(30),\n                   data text) tablespace tbs_app_test_tbl;\n\nselect current_database(),\n       pg_get_userbyid(pc.relowner) as table_owner,\n\tpsat.relname as table_name,\n\tpsat.schemaname as table_schema,\n\tcase when pc.reltablespace = 0 then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n\t                                else (select pt.spcname from pg_tablespace pt where pt.oid = pc.reltablespace)::name end as table_tablespace\nfrom pg_class pc\njoin pg_stat_all_tables psat on psat.relid = pc.oid\nwhere psat.schemaname not in ('pg_toast','pg_catalog','information_schema')\n--psat.relname in ('table_name')\norder by table_owner,\n\t  table_name;\n\nselect schemaname\n      ,tablename\n      ,indexname\n      ,case when pi.tablespace is null then (select pt.spcname from pg_database pd join pg_tablespace pt on pd.dattablespace = pt.oid where pd.datname = (select current_database()))::name\n            else pi.tablespace end as table_tablespace\nfrom pg_indexes pi\nwhere schemaname in ('schema_user_app_test_01')\norder by schemaname\n        ,tablename\n        ,indexname;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewbox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D8DEE9FF\">psql <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">h localhost <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">p <\/span><span style=\"color: #B48EAD\">5432<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">U user_app_test <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\">d db_test<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">, session_user, current_user, current_schema;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">schema_user_app_test_01<\/span><span style=\"color: #D8DEE9FF\">.table_aux_01 (id <\/span><span style=\"color: #81A1C1\">serial<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                   <\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">30<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                   <\/span><span style=\"color: #81A1C1\">data<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">text<\/span><span style=\"color: #D8DEE9FF\">) tablespace tbs_app_test_tbl;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">index<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">idx_table_aux_01_col_id<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> schema_user_app_test_01.table_aux_01(id) tablespace tbs_app_test_idx;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">create<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">table<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">schema_user_app_test_02<\/span><span style=\"color: #D8DEE9FF\">.table_aux_02 (id <\/span><span style=\"color: #81A1C1\">serial<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                   <\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">varchar<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #B48EAD\">30<\/span><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                   <\/span><span style=\"color: #81A1C1\">data<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">text<\/span><span style=\"color: #D8DEE9FF\">) tablespace tbs_app_test_tbl;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">       pg_get_userbyid(pc.relowner) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_owner,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\tpsat.relname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_name,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\tpsat.schemaname <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_schema,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t                                <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.reltablespace)::<\/span><span style=\"color: #81A1C1\">name<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_class pc<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_stat_all_tables psat <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> psat.relid <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pc.oid<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> psat.schemaname <\/span><span style=\"color: #81A1C1\">not<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_toast<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">pg_catalog<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">,<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">information_schema<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #616E88\">--psat.relname in (&#39;table_name&#39;)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> table_owner,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">\t  table_name;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,indexname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">      ,<\/span><span style=\"color: #81A1C1\">case<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">when<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">is<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">then<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> pt.spcname <\/span><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_database pd <\/span><span style=\"color: #81A1C1\">join<\/span><span style=\"color: #D8DEE9FF\"> pg_tablespace pt <\/span><span style=\"color: #81A1C1\">on<\/span><span style=\"color: #D8DEE9FF\"> pd.dattablespace <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> pt.oid <\/span><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> pd.datname <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">select<\/span><span style=\"color: #D8DEE9FF\"> current_database<\/span><span style=\"color: #ECEFF4\">()<\/span><span style=\"color: #D8DEE9FF\">))::<\/span><span style=\"color: #81A1C1\">name<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">else<\/span><span style=\"color: #D8DEE9FF\"> pi.tablespace <\/span><span style=\"color: #81A1C1\">end<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> table_tablespace<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">from<\/span><span style=\"color: #D8DEE9FF\"> pg_indexes pi<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">where<\/span><span style=\"color: #D8DEE9FF\"> schemaname <\/span><span style=\"color: #81A1C1\">in<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">schema_user_app_test_01<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">order by<\/span><span style=\"color: #D8DEE9FF\"> schemaname<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,tablename<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        ,indexname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p><\/p>\n\n\n\n<figure data-wp-context=\"{&quot;imageId&quot;:&quot;69fdea85bd64b&quot;}\" data-wp-interactive=\"core\/image\" data-wp-key=\"69fdea85bd64b\" class=\"wp-block-image size-full wp-lightbox-container\"><img loading=\"lazy\" decoding=\"async\" width=\"1321\" height=\"953\" data-wp-class--hide=\"state.isContentHidden\" data-wp-class--show=\"state.isContentVisible\" data-wp-init=\"callbacks.setButtonStyles\" data-wp-on--click=\"actions.showLightbox\" data-wp-on--load=\"callbacks.setButtonStyles\" data-wp-on-window--resize=\"callbacks.setButtonStyles\" src=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/05.png\" alt=\"\" class=\"wp-image-440\" srcset=\"https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/05.png 1321w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/05-300x216.png 300w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/05-1024x739.png 1024w, https:\/\/lemosdba.com.br\/wp-content\/uploads\/2025\/05\/05-768x554.png 768w\" sizes=\"auto, (max-width: 1321px) 100vw, 1321px\" \/><button\n\t\t\tclass=\"lightbox-trigger\"\n\t\t\ttype=\"button\"\n\t\t\taria-haspopup=\"dialog\"\n\t\t\taria-label=\"Enlarge\"\n\t\t\tdata-wp-init=\"callbacks.initTriggerButton\"\n\t\t\tdata-wp-on--click=\"actions.showLightbox\"\n\t\t\tdata-wp-style--right=\"state.imageButtonRight\"\n\t\t\tdata-wp-style--top=\"state.imageButtonTop\"\n\t\t>\n\t\t\t<svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"12\" height=\"12\" fill=\"none\" viewbox=\"0 0 12 12\">\n\t\t\t\t<path fill=\"#fff\" d=\"M2 0a2 2 0 0 0-2 2v2h1.5V2a.5.5 0 0 1 .5-.5h2V0H2Zm2 10.5H2a.5.5 0 0 1-.5-.5V8H0v2a2 2 0 0 0 2 2h2v-1.5ZM8 12v-1.5h2a.5.5 0 0 0 .5-.5V8H12v2a2 2 0 0 1-2 2H8Zm2-12a2 2 0 0 1 2 2v2h-1.5V2a.5.5 0 0 0-.5-.5H8V0h2Z\" \/>\n\t\t\t<\/svg>\n\t\t<\/button><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Considerations<\/h1>\n\n\n\n<p class=\"has-medium-font-size\">Ensuring proper organization of the database is very important. Therefore, make sure that new resources have the correct definitions and that existing ones are properly configured. When making changes to objects, keep in mind that they may become temporarily unavailable, which can negatively impact others (applications, users, routines, etc.).<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">References<\/h1>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createdatabase.html\">CREATE DATABASE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createtablespace.html\">CREATE TABLESPACE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createschema.html\">CREATE SCHEMA<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createtable.html\">CREATE TABLE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-altertable.html\">ALTER TABLE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createindex.html\">CREATE INDEX<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-alterindex.html\">ALTER INDEX<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-grant.html\">GRANT<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createuser.html\">CREATE USER<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/sql-createrole.html\">CREATE ROLE<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/catalog-pg-class.html\">pg_class<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/view-pg-indexes.html\">pg_indexes<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW\">pg_stat_all_tables<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/view-pg-locks.html\">pg_locks<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW\">pg_stat_activity<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/catalog-pg-database.html\">pg_database<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/17\/catalog-pg-tablespace.html\">pg_tablespace<\/a><\/li>\n<\/ol>\n\n\n\n<p><\/p>","protected":false},"excerpt":{"rendered":"<p>The definitions of owner, schema, and tablespace for table and index objects will be addressed, as well as how to modify them.<\/p>","protected":false},"author":1,"featured_media":449,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[9],"tags":[24,20,19,25,21,23,22],"class_list":["post-363","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-index","tag-object","tag-postgresql","tag-privileges","tag-schema","tag-table","tag-tablespace"],"_links":{"self":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/posts\/363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/comments?post=363"}],"version-history":[{"count":74,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/posts\/363\/revisions"}],"predecessor-version":[{"id":1062,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/posts\/363\/revisions\/1062"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/media\/449"}],"wp:attachment":[{"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/media?parent=363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/categories?post=363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lemosdba.com.br\/en\/wp-json\/wp\/v2\/tags?post=363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}