powershell – Shell script that assigns psql values ​​to variables

I am trying to assign a query output value to the variable in the sh script file. While I am running on the Git bash command line, it throws an error in the assignment.

mycount = $(psql -X -A -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d${PGDATABASE} -c "SELECT max(date_posted) FROM posts;" ) 
echo "max date is :" $mycount

This is my exit screen:
enter the description of the image here

I don't know how to solve this problem. Please, someone help with this.

postgresql – How to configure psql command line arguments in an sql script?

The general question is can you set psql command line arguments within an sql script? Something similar to SET

In particular, Postgresql psql has a -S option, which activates –single-line. According to postgresql documentation, this does the following:

   Runs in single-line mode where a newline terminates an SQL command, as a semicolon does.

I have a sql file and I want to pass it with the -f option. Is there a way to establish a new line that terminates a sql command in a file?

e.g.
//do something to set --single-line
create table t1
select * from t1 

I know you can run a bash script with an sql file, something like

 psql -S ...

I guess the broader question is

postgresql – I can connect to my database through pgadmin, but I can not connect to the database using the terminal (psql)

I have created a database using the pgadmin GUI and it works fine. I can connect to the server with my password. But when I want to connect to the database using psql in the terminal, after entering the password, it says "psql: FATAL: password authentication error for the user" {username} "". I tried all the possible passwords, but it still does not work.

Has anyone solved this problem before?

psql: FATAL: password authentication error for user "{username}"

psql – In the PostgreSQL URL I can not use a password that contains special characters

I have a postgresql database in Azure

the password The string has special characters like these:

) mvd99 / i and H_ = ag = Por / W} %% aKY ^ ygt +, sC7 %% P? APOU

psql --dbname = postgresql: // db_user @ mydemoserver :) mvd99 / iyH_ = ag = Por / W} %% aKY ^ ygt +, sC7 %% P? APOU@mydemoserver.postgres.database.azure.com: 5432 / mydb

When I run the psql command, it shows me the following error:

psql: invalid percentage code token: "iyH_ = ag = Por / W} %% aKY ^ ygt +, sC7 %% P"

I already tried putting the password string in quotation marks but it does not work

What other options do I have?
I can not change the password

I'm interested in using the psql client, because I have to make very large backups

PSQL: syntax error in or near "AS"

I have a program that takes one or more of the names of a table, a list stored in a reference table and executes a SELECT query in it. so I retrieve the names of the tables and insert it into the SELECT query in a loop.

(SELECT ACCOUNT (*) FROM
(SELECT col1, col2, col3, col4 FROM table1
UNION
SELECT col1, col2, col3, col4 FROM table2
UNION
SELECT col1, col2, col3, col4 FROM table3
UNION
SELECT col1, col2, col3, col4 FROM table4
) t1
WHERE
// where the conditions
As name

This is the error

Query 1 ERROR: ERROR: syntax error in or near "AS"
LINE 21: AS name

the query works if the AS clause is not present
^

postgresql – The query returns results in pgAdmin running on a remote client but not directly through psql on the db server

When I request my database using a role in pgAdmin on a remote client, I get the results of the query. However, if I run the same query directly on the db server with the same role, I do not see the result of the query. I see that the table is present with the command " dt" but I can not see the tables. Do you need any configuration to enable the query of the database from the same host?

postgresql – PSQL incomplete backup: how to debug

I have been backing up my "screen" database.

after connecting to PSQL and writing l +

I am receiving (among other things):

Name | Owner | Coding | Check Ctype | Access privileges | Size | Table space | Description
------------------ + ------- + ---------- + ------------ - + ------------- + ------------------- + --------- + ---- -------- + ----------------------------------------- ---
screen | admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 36 GB | pg_default |

The size of my database is around 36 GB.

Now I usually make regular backups doing:

pg_dumb screen> screenbackup.bak

And the size of the output was always quite consistent with the size of my database.

But today I have a backup of only 8 GB and that seems very strange to me.

After restoring to a temporary database and making a query, some data seems to be missing.

The size of the restored data was 10GB.

Thanks, I never dropped the original in the first place, but there always seems to be a problem. I can not have pg_dump or pg_dumpall back up the entire database. The size is inconsistent when, in my experience, it is often at least the same size as the database or larger. Not 4 times smaller ..

Do you have any idea where to go to know where the problem is coming from?

Editing: maybe it's important: I'm in PSQL 9.4.4.1 and trying to make the backup so I can update psql as well as just save my data.

postgresql – Why are the psql commands in my script suddenly killed by jenkins / hudson?

I have an existing jenkins job that starts a shell script to copy my prod environment into qa.

We added a lot of data to prod (gzip dump went from 2gig to 15gig) and, suddenly, my jobs in jenkins started to fail.

We are running postgres 9.5 in aws and jenkins 2,171. all jenkins jobs are executed in the master, which is the same server with 6 executors. No memory problems / cpu / disk space

I tried some things: statement_timeout In the instance of Postgres it is already 0. Going from bash to sh for some reason helped in some scripts but not in others. In particular, it still has several psql statements removed. The script works well when it is executed from an interactive shell.

We also tried disabling Process Tree Killer https://wiki.jenkins.io/display/JENKINS/ProcessTreeKiller. do not go.

Here is the code of two of the most innocuous commands that should be executed fairly quickly. $ POSTGRES_HOST_OPTS It only has the name of the database and the port:

echo -e "Executing the POSTGIS command"
psql $ POSTGRES_HOST_OPTS -U $ POSTGRES_ENV_POSTGRES_USER_PROD -d postgres -c "CREATE EXTENSION postgis;"

echo -e "Creating the temporary user dv3_qa_tmp so that we can change the name of $ POSTGRES_ENV_POSTGRES_USER_PROD user  n"
psql $ POSTGRES_HOST_OPTS -U $ POSTGRES_ENV_POSTGRES_USER_PROD -d postgres -c "create role dv3_qa_tmp password & # 39; $ PGPASSWORD_QA & # 39; createdb createrole heredit login;"

Here is the jenkins console output:

Waiting for the new instance to be available ...
-e Renaming the dv3_prod database to dv3_qa

Delicate
-e Executing the POSTGIS command
Delicate
-e Creating the temporary user dv3_qa_tmp so that we can change the user name dv3_prod_user

Delicate
-e Rename the user dv3_prod_user to dv3_qa_user

Delicate
Delicate
-my
All ready

From the jenkins.log file there is something in the file descriptors, but I'm not sure how that relates. I have also tried to redirect stderr that removes this message but does not prevent the commands from being killed.

April 10, 2019 16:23:31 hudson.Proc $ LocalProc join
WARNING: Process the filtered file descriptors. See https://jenkins.io/redirect/troubleshooting/process-leaked-file-descriptors for more information.
java.lang.Exception
in hudson.Proc $ LocalProc.join (Proc.java:334)
in hudson.tasks.CommandInterpreter.join (CommandInterpreter.java:155)
in hudson.tasks.CommandInterpreter.perform (CommandInterpreter.java:109)
in hudson.tasks.CommandInterpreter.perform (CommandInterpreter.java:66)
in hudson.tasks.BuildStepMonitor $ 1.perform (BuildStepMonitor.java:20)
in hudson.model.AbstractBuild $ AbstractBuildExecution.perform (AbstractBuild.java:741)
in hudson.model.Build $ BuildExecution.build (Build.java:206)
in hudson.model.Build $ BuildExecution.doRun (Build.java:163)
in hudson.model.AbstractBuild $ AbstractBuildExecution.run (AbstractBuild.java:504)
in hudson.model.Run.execute (Run.java:1818)
in hudson.model.FreeStyleBuild.run (FreeStyleBuild.java:43)
in hudson.model.ResourceController.execute (ResourceController.java:97)
in hudson.model.Executor.run (Executor.java:429)

How psql to a specific PG server

There are PG 9.5 and 11 installed and I would like psql Command line to the server 11. How can I do that? may l psql to the command line but there are only tables from the 9.5 server available. I am also of use pgadmin 4.

postgresql – Table psql Description

in psql if I do d +, I get something like

    Scheme | Name | Type | Owner | Size | Description
-------- + ------------------------- + ---------- + ---- ---- + ------------ + -------------
Norsed Routing Result | table | morten 16 kB |
norsar Routing Result_id_seq | sequence | morten 8192 bytes | 

and so. The last field, "Description" is always blank. Where does that field read its content? – and therefore, how can I establish a description in a table? In some cases, that could be quite useful, the same for the description of the columns that are shown if I do d +.

I tried set ECHO_HIDDEN ON, but that did not make it much clearer.